agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedfunction INPUT as whole table
7+ messages / 5 participants
[nested] [flat]
* function INPUT as whole table
@ 2021-06-15 10:59 Yambu <hyambu@gmail.com>
2021-06-15 12:41 ` Re: function INPUT as whole table David G. Johnston <david.g.johnston@gmail.com>
2021-06-15 13:33 ` Re: function INPUT as whole table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Yambu @ 2021-06-15 10:59 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
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) )
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function INPUT as whole table
2021-06-15 10:59 function INPUT as whole table Yambu <hyambu@gmail.com>
@ 2021-06-15 12:41 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-15 14:01 ` Re: function INPUT as whole table Viswanatha Sastry <medipalli@yahoo.com>
2021-06-15 17:40 ` Re: function INPUT as whole table Дмитрий Иванов <firstdismay@gmail.com>
1 sibling, 2 replies; 7+ messages in thread
From: David G. Johnston @ 2021-06-15 12:41 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function INPUT as whole table
2021-06-15 10:59 function INPUT as whole table Yambu <hyambu@gmail.com>
2021-06-15 12:41 ` Re: function INPUT as whole table David G. Johnston <david.g.johnston@gmail.com>
@ 2021-06-15 14:01 ` Viswanatha Sastry <medipalli@yahoo.com>
2021-06-15 14:07 ` Re: function INPUT as whole table Yambu <hyambu@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Viswanatha Sastry @ 2021-06-15 14:01 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function INPUT as whole table
2021-06-15 10:59 function INPUT as whole table Yambu <hyambu@gmail.com>
2021-06-15 12:41 ` Re: function INPUT as whole table David G. Johnston <david.g.johnston@gmail.com>
2021-06-15 14:01 ` Re: function INPUT as whole table Viswanatha Sastry <medipalli@yahoo.com>
@ 2021-06-15 14:07 ` Yambu <hyambu@gmail.com>
2021-06-16 01:50 ` Re: function INPUT as whole table Дмитрий Иванов <firstdismay@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Yambu @ 2021-06-15 14:07 UTC (permalink / raw)
To: Viswanatha Sastry <medipalli@yahoo.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function INPUT as whole table
2021-06-15 10:59 function INPUT as whole table Yambu <hyambu@gmail.com>
2021-06-15 12:41 ` Re: function INPUT as whole table David G. Johnston <david.g.johnston@gmail.com>
2021-06-15 14:01 ` Re: function INPUT as whole table Viswanatha Sastry <medipalli@yahoo.com>
2021-06-15 14:07 ` Re: function INPUT as whole table Yambu <hyambu@gmail.com>
@ 2021-06-16 01:50 ` Дмитрий Иванов <firstdismay@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Дмитрий Иванов @ 2021-06-16 01:50 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: Viswanatha Sastry <medipalli@yahoo.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
>>
>>
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function INPUT as whole table
2021-06-15 10:59 function INPUT as whole table Yambu <hyambu@gmail.com>
2021-06-15 12:41 ` Re: function INPUT as whole table David G. Johnston <david.g.johnston@gmail.com>
@ 2021-06-15 17:40 ` Дмитрий Иванов <firstdismay@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Дмитрий Иванов @ 2021-06-15 17:40 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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 г. в 17:41, David G. Johnston <david.g.johnston@gmail.com>:
>
> 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.
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function INPUT as whole table
2021-06-15 10:59 function INPUT as whole table Yambu <hyambu@gmail.com>
@ 2021-06-15 13:33 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2021-06-15 13:33 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Yambu <hyambu@gmail.com> writes:
> Is it possible to have as input to a function , a whole table contents, if
> the table is a small lookup table?
Not directly, but you might be able to achieve the effect by opening a
cursor and passing the cursor name to the function, which could fetch
from it. See the examples in the cursor section of the plpgsql manual.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-06-16 01:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-15 10:59 function INPUT as whole table Yambu <hyambu@gmail.com>
2021-06-15 12:41 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-15 14:01 ` Viswanatha Sastry <medipalli@yahoo.com>
2021-06-15 14:07 ` Yambu <hyambu@gmail.com>
2021-06-16 01:50 ` Дмитрий Иванов <firstdismay@gmail.com>
2021-06-15 17:40 ` Дмитрий Иванов <firstdismay@gmail.com>
2021-06-15 13:33 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox