[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: function INPUT as whole table
From: Дмитрий Иванов <firstdismay () gmail ! com>
Date: 2021-06-16 1:50:31
Message-ID: CAPL5KHp7ddbX77i6NqHGNUrWKWBTEo9U5gVehJv6kS847spWnw () mail ! gmail ! com
[Download RAW message or body]
This is true. But there is a useful trick. When sampling a moderate amount
of data, you can use an array as an argument. This allows for prefetching
from a large dataset and restricting data binding.
CREATE OR REPLACE FUNCTION
bpd.int_object_ext_prop_by_id_object_array(*object_array
bigint[]*)
RETURNS SETOF bpd.int_object_ext
LANGUAGE plpgsql
*STABLE PARALLEL SAFE*
AS $function$
DECLARE
BEGIN
RETURN QUERY SELECT
op.id_object_carrier AS id,
array_agg((op.*)::bpd.cobject_prop) AS property_list
FROM bpd.vobject_prop op
WHERE (op.id_object_carrier = *ANY(object_array)*)
GROUP BY op.id_object_carrier;
END;
$function$;
---------------------------------------
CREATE OR REPLACE FUNCTION
bpd.object_prop_user_small_agg_func_find_ext(iid_global_prop bigint,
find_mask character varying)
RETURNS SETOF bpd.vobject_general_ext
LANGUAGE plpgsql
*STABLE PARALLEL SAFE*
AS $function$
DECLARE
object_array BIGINT[]; --Массив объектов
fglobal_prop "bpd"."vglobal_prop"%ROWTYPE; --Глобальное свойство
BEGIN
SELECT * INTO fglobal_prop FROM ONLY "bpd"."vglobal_prop" WHERE id =
iid_global_prop;
IF NOT(fglobal_prop IS NULL) THEN
CASE fglobal_prop.id_prop_type
WHEN 1 THEN
*object_array* = (SELECT array_agg(o.id) ...)
);
WHEN 2 THEN
find_mask = bpd.int_is_numberic(find_mask);
*object_array* = (SELECT array_agg(o.id) ...)
END CASE;
RETURN QUERY
SELECT
o.id,
o.name,
o.id_conception,
...
o_ext.property_list,
p_path.path,
cr.round
FROM bpd.object o
JOIN bpd.class_snapshot cp ON (o."id" = ANY(object_array)) AND
(cp.id = o.id_class) AND (cp."timestamp" = o.timestamp_class)
LEFT JOIN bpd.conception con ON (con.id = o.id_conception)
LEFT JOIN bpd.unit_conversion_rules cr ON (cr.id =
o.id_unit_conversion_rule)
LEFT JOIN bpd.int_position_path p_path ON (p_path.id =
o.id_position)
LEFT JOIN
* bpd.int_object_ext_prop_by_id_object_array(object_array)* o_ext ON (
o_ext.id = o.id)
ORDER BY o.name;
END IF;
END;
$function$;
-- -------------------------------------------------------------
In this way, I managed to significantly increase productivity.
вт, 15 июн. 2021 г. в 19:07, Yambu <hyambu@gmail.com>:
> ok, thank you all
>
> On Tue, Jun 15, 2021 at 4:01 PM Viswanatha Sastry <medipalli@yahoo.com>
> wrote:
>
>> You can send it as a json object from App. in PostgreSQL it will be
>> string datatype as parameter and you can insert into a temp table with json
>> function.
>>
>> Thanks & Regards
>> Viswanatha Shastry M.
>> Hyderabad,
>> Phone Mobile : 9493050037
>>
>>
>> On Tuesday, 15 June, 2021, 06:11:20 pm IST, David G. Johnston <
>> david.g.johnston@gmail.com> wrote:
>>
>>
>>
>> On Tuesday, June 15, 2021, Yambu <hyambu@gmail.com> wrote:
>>
>> Hello
>>
>> Is it possible to have as input to a function , a whole table contents,
>> if the table is a small lookup table?
>>
>> Something like this
>>
>> CREATE or replace function test (IN (select * from table1) )
>>
>>
>> No. Function arguments are data types, not contents. Queries go inside
>> the function body.
>>
>> David J.
>>
>>
>>
>
[Attachment #3 (text/html)]
<div dir="ltr">This is true. But there is a useful trick. When sampling a moderate \
amount of data, you can use an array as an argument. This allows for prefetching from \
a large dataset and restricting data binding.<br><br>CREATE OR REPLACE FUNCTION \
bpd.int_object_ext_prop_by_id_object_array(<u><b>object_array bigint[]</b></u>)<br> \
RETURNS SETOF bpd.int_object_ext<br> LANGUAGE plpgsql<br> <b><u>STABLE PARALLEL \
SAFE</u></b><br>AS $function$<br>DECLARE<br><br>BEGIN<br> RETURN QUERY \
SELECT<br> op.id_object_carrier AS id,<br> \
array_agg((op.*)::bpd.cobject_prop) AS property_list<br> FROM bpd.vobject_prop \
op<br> WHERE (op.id_object_carrier = <b><u>ANY(object_array)</u></b>)<br> \
GROUP BY op.id_object_carrier;<br>END;<br>$function$;<br><br>---------------------------------------<br>CREATE \
OR REPLACE FUNCTION bpd.object_prop_user_small_agg_func_find_ext(iid_global_prop \
bigint, find_mask character varying)<br> RETURNS SETOF bpd.vobject_general_ext<br> \
LANGUAGE plpgsql<br> <b><u>STABLE PARALLEL SAFE</u></b><br>AS \
$function$<br>DECLARE<br> object_array BIGINT[]; --Массив \
объектов<br> fglobal_prop \
"bpd"."vglobal_prop"%ROWTYPE; --Глобальное \
свойство<br>BEGIN<br> SELECT * INTO fglobal_prop FROM ONLY \
"bpd"."vglobal_prop" WHERE id = iid_global_prop;<br>IF \
NOT(fglobal_prop IS NULL) THEN<br> CASE fglobal_prop.id_prop_type<br> \
WHEN 1 THEN<br> <b><u>object_array</u></b> = (SELECT \
array_agg(<a href="http://o.id/" target="_blank">o.id</a>) ...)<br> \
);<br> WHEN 2 THEN<br> find_mask = \
bpd.int_is_numberic(find_mask);<br> \
<b><u>object_array</u></b> = (SELECT array_agg(<a href="http://o.id/" \
target="_blank">o.id</a>) ...)<br> END CASE;<br> <br> \
RETURN QUERY<br> SELECT<br> <a \
href="http://o.id/" target="_blank">o.id</a>,<br> <a \
href="http://o.name/" target="_blank">o.name</a>,<br> \
o.id_conception,<br> ...<br> \
o_ext.property_list,<br> p_path.path,<br> \
cr.round<br> FROM bpd.object o<br> JOIN \
bpd.class_snapshot cp ON (o."id" = ANY(object_array)) AND (<a \
href="http://cp.id/" target="_blank">cp.id</a> = o.id_class) AND \
(cp."timestamp" = o.timestamp_class)<br> LEFT JOIN \
bpd.conception con ON (<a href="http://con.id/" target="_blank">con.id</a> = \
o.id_conception)<br> LEFT JOIN bpd.unit_conversion_rules cr ON (<a \
href="http://cr.id/" target="_blank">cr.id</a> = o.id_unit_conversion_rule)<br> \
LEFT JOIN bpd.int_position_path p_path ON (<a href="http://p_path.id/" \
target="_blank">p_path.id</a> = o.id_position)<br> LEFT \
JOIN<b><u> bpd.int_object_ext_prop_by_id_object_array(object_array)</u></b> o_ext \
ON (<a href="http://o_ext.id/" target="_blank">o_ext.id</a> = <a \
href="http://o.id/" target="_blank">o.id</a>)<br> ORDER BY <a \
href="http://o.name/" target="_blank">o.name</a>; <br> END \
IF; <br>END;<br>$function$;<br>-- \
-------------------------------------------------------------<br>In this way, I \
managed to significantly increase productivity.<br></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">вт, 15 июн. 2021 г. в \
19:07, Yambu <<a href="mailto:hyambu@gmail.com">hyambu@gmail.com</a>>:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">ok, thank you all</div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Jun 15, 2021 at 4:01 PM \
Viswanatha Sastry <<a href="mailto:medipalli@yahoo.com" \
target="_blank">medipalli@yahoo.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div><div style="font-family:"times new \
roman","new york",times,serif;font-size:16px"><div><div dir="ltr">You \
can send it as a json object from App. in PostgreSQL it will be string datatype as \
parameter and you can insert into a temp table with json \
function.</div><div><br></div><div>Thanks & Regards <div>Viswanatha Shastry M. \
</div><div>Hyderabad, </div><div>Phone Mobile : 9493050037</div></div></div> \
<div><br></div><div><br></div>
</div><div id="gmail-m_-402599951162347282gmail-m_-8035668641014643955yahoo_quoted_4562030171">
<div style="font-family:"Helvetica \
Neue",Helvetica,Arial,sans-serif;font-size:13px;color:rgb(38,40,42)">
<div>
On Tuesday, 15 June, 2021, 06:11:20 pm IST, David G. Johnston \
<<a href="mailto:david.g.johnston@gmail.com" \
target="_blank">david.g.johnston@gmail.com</a>> wrote: </div>
<div><br></div>
<div><br></div>
<div><div \
id="gmail-m_-402599951162347282gmail-m_-8035668641014643955yiv4584774629"><div><br \
clear="none">On Tuesday, June 15, 2021, Yambu <<a rel="nofollow noopener \
noreferrer" shape="rect" href="mailto:hyambu@gmail.com" \
target="_blank">hyambu@gmail.com</a>> wrote:<br clear="none"><div \
id="gmail-m_-402599951162347282gmail-m_-8035668641014643955yiv4584774629yqt40645"><blockquote \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hello<br clear="none"><div><br \
clear="none"></div><div>Is it possible to have as input to a function , a whole table \
contents, if the table is a small lookup table?</div><div><br \
clear="none"></div><div>Something like this </div><div> <br \
clear="none"></div><div>CREATE or replace function test (IN (select * from table1) ) \
</div></div></blockquote></div><div><br clear="none"></div><div>No. Function \
arguments are data types, not contents. Queries go inside the function \
body.</div><div><br clear="none"></div><div>David J.</div><div> </div><div> </div> \
</div></div></div> </div>
</div></div></blockquote></div>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic