agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedselect into composite type / return
14+ messages / 4 participants
[nested] [flat]
* select into composite type / return
@ 2021-03-17 16:48 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 14+ messages in thread
From: Gary Stainburn @ 2021-03-17 16:48 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
I have a function that takes 7 numerical inputs, performs calculations,
and then returns a composite type.
create type breakdown as
f1 numeric(9,2),
f2 numeric(9,2),
f3 numeric(9,2),
f4 numeric(9,2),
f5 numeric(9,2),
f6 numeric(9,2)
);
create function do_breakdown(
v1 numeric(9,2),
v2 numeric(9,2),
v3 numeric(9,2),
v4 numeric(9,2),
v5 numeric(9,2),
v6 numeric(9,2),
v7 numeric(9,2)
) returns breakdown as $$
DECLARE
D breakdown;
BEGIN
-- calculate breakdown
return D;
END;
$$
LANGUAGE PLPGSQL;
This works great, returning one row with the separate columns.
I now want to set up another function which will take a key, retrieve
the arguments from a table, and call the first function. The problem is
that I can't get the syntax correct to return the composite type. I
have tried
create function do_breakdown(key text) returns breakdown as $$
DECLARE
v RECORD;
BEGIN
select into v * from stored s where key s.key = key;
RETURN do_breakdown(v.f1,v.f2,v.f3,v.f4,v.f5,v.f6);
END;
$$
LANGUAGE PLPGSQL;
but it returns the whole thing as a single column. Adding a typecase
didn't help.
I have also tried
create function do_breakdown(key text) returns breakdown as $$
DECLARE
v RECORD;
D breakdown;
BEGIN
select into v * from stored s where key s.key = key;
select into D * from do_breakdown(v.f1,v.f2,v.f3,v.f4,v.f5,v.f6);
RETURN D;
END;
$$
LANGUAGE PLPGSQL;
but that also returns everything as a single column. Any help would be
appreciated.
Gary
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-17 17:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2021-03-17 17:26 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> I have a function that takes 7 numerical inputs, performs calculations,
> and then returns a composite type.
> create type breakdown as
> f1 numeric(9,2),
> f2 numeric(9,2),
> f3 numeric(9,2),
> f4 numeric(9,2),
> f5 numeric(9,2),
> f6 numeric(9,2)
> );
> create function do_breakdown(
> v1 numeric(9,2),
> v2 numeric(9,2),
> v3 numeric(9,2),
> v4 numeric(9,2),
> v5 numeric(9,2),
> v6 numeric(9,2),
> v7 numeric(9,2)
> ) returns breakdown as $$
> DECLARE
> D breakdown;
> BEGIN
> -- calculate breakdown
> return D;
> END;
> $$
> LANGUAGE PLPGSQL;
> This works great, returning one row with the separate columns.
> I now want to set up another function which will take a key, retrieve
> the arguments from a table, and call the first function. The problem is
> that I can't get the syntax correct to return the composite type. I
> have tried
> create function do_breakdown(key text) returns breakdown as $$
> DECLARE
> v RECORD;
> BEGIN
> select into v * from stored s where key s.key = key;
> RETURN do_breakdown(v.f1,v.f2,v.f3,v.f4,v.f5,v.f6);
> END;
> $$
> LANGUAGE PLPGSQL;
> but it returns the whole thing as a single column.
AFAICS these two functions will have exactly the same output behavior,
ie returning a "breakdown" composite type. If they act differently
for you, either you are calling them in different ways or you made
a mistake somewhere. I can't help noticing that the RETURN in the
second function is calling a six-argument function, which is not the
one you showed first. Maybe that version of do_breakdown() returns
something different?
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-17 17:42 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Gary Stainburn @ 2021-03-17 17:42 UTC (permalink / raw)
To: ; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On 17/03/2021 17:26, Tom Lane wrote:
> AFAICS these two functions will have exactly the same output behavior,
> ie returning a "breakdown" composite type. If they act differently
> for you, either you are calling them in different ways or you made
> a mistake somewhere. I can't help noticing that the RETURN in the
> second function is calling a six-argument function, which is not the
> one you showed first. Maybe that version of do_breakdown() returns
> something different?
>
> regards, tom lane
Hi Tom,
Thanks for the quick response. The missing argument was because I had
to use pseudo details to protect intellectual property and I miss-typed
the code.
You were correct. The problem was not with the functions, but with how
I was calling them.
Gary
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-18 10:05 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 14+ messages in thread
From: Gary Stainburn @ 2021-03-18 10:05 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
I now have the working functions.
The first accepts 7 arguments and returns a composite type of the
calculations breakdown.
The second takes a single argument and retrieves the 7 arguments from a
table before calling the first argument.
What I can't get my head round is how I can use these functions to
return a setof breakdowns. All I can get is thebreakdown returned as a
single column.
All advice welcome.
users=# select * from do_breakdown(1);
f1 | f2 | f3 | f4 | f5 | f6
------+------+------+------+------+------
1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 6.00
(1 row)
users=# select * from sessions;
id | v1 | v2 | v3 | v4 | v5 | v6 | v7
----+-------+-------+-------+-------+-------+-------+-------
1 | 1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 6.00 | 7.00
2 | 11.00 | 12.00 | 13.00 | 14.00 | 15.00 | 16.00 | 17.00
3 | 21.00 | 22.00 | 23.00 | 24.00 | 25.00 | 26.00 | 27.00
(3 rows)
users=# select id, do_breakdown(id) from sessions where id in (1,3);
id | do_breakdown
----+---------------------------------------
1 | (1.00,2.00,3.00,4.00,5.00,6.00)
3 | (21.00,22.00,23.00,24.00,25.00,26.00)
(2 rows)
users=#
create type breakdown as (
f1 numeric(9,2),
f2 numeric(9,2),
f3 numeric(9,2),
f4 numeric(9,2),
f5 numeric(9,2),
f6 numeric(9,2)
);
create table sessions (
ID int4 not null primary key,
v1 numeric(9,2),
v2 numeric(9,2),
v3 numeric(9,2),
v4 numeric(9,2),
v5 numeric(9,2),
v6 numeric(9,2),
v7 numeric(9,2)
);
insert into sessions values
(1,1,2,3,4,5,6,7),(2,11,12,13,14,15,16,17),(3,21,22,23,24,25,26,27);
create or replace function do_breakdown(
v1 numeric(9,2),
v2 numeric(9,2),
v3 numeric(9,2),
v4 numeric(9,2),
v5 numeric(9,2),
v6 numeric(9,2),
v7 numeric(9,2)
) returns breakdown as $$
DECLARE
D breakdown;
BEGIN
-- calculate breakdown
D.f1=v1;
D.f2=v2;
D.f3=v3;
D.f4=v4;
D.f5=v5;
D.f6=v6;
return D;
END;
$$
LANGUAGE PLPGSQL;
create or replace function do_breakdown(vID int4) RETURNS breakdown
AS $$
DECLARE
v RECORD;
D breakdown;
BEGIN
IF vID IS NULL THEN RETURN NULL; END IF;
select into v * from sessions s where s.ID = vID;
IF NOT FOUND THEN
RAISE NOTICE 'breakdown: % not found',vID;
RETURN NULL;
END IF;
RETURN do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
END;
$$
LANGUAGE PLPGSQL;
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-18 11:10 Torsten Grust <torsten.grust@gmail.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 14+ messages in thread
From: Torsten Grust @ 2021-03-18 11:10 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi Gary,
a shot in the dark but maybe
SELECT id, (do_breakdown(id)).*
FROM ...
already does the job? I'm on 13.2 here and my quickly whipped up example
shows the desired behavior:
# SELECT (f(1)).*;
a | b | c
---+---+---
1 | 2 | 3
Best wishes,
—Torsten
On Thu, Mar 18, 2021 at 11:05 AM Gary Stainburn <
gary.stainburn@ringways.co.uk> wrote:
> I now have the working functions.
>
> The first accepts 7 arguments and returns a composite type of the
> calculations breakdown.
> The second takes a single argument and retrieves the 7 arguments from a
> table before calling the first argument.
>
> What I can't get my head round is how I can use these functions to
> return a setof breakdowns. All I can get is thebreakdown returned as a
> single column.
>
> All advice welcome.
>
> users=# select * from do_breakdown(1);
> f1 | f2 | f3 | f4 | f5 | f6
> ------+------+------+------+------+------
> 1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 6.00
> (1 row)
>
> users=# select * from sessions;
> id | v1 | v2 | v3 | v4 | v5 | v6 | v7
> ----+-------+-------+-------+-------+-------+-------+-------
> 1 | 1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 6.00 | 7.00
> 2 | 11.00 | 12.00 | 13.00 | 14.00 | 15.00 | 16.00 | 17.00
> 3 | 21.00 | 22.00 | 23.00 | 24.00 | 25.00 | 26.00 | 27.00
> (3 rows)
>
> users=# select id, do_breakdown(id) from sessions where id in (1,3);
> id | do_breakdown
> ----+---------------------------------------
> 1 | (1.00,2.00,3.00,4.00,5.00,6.00)
> 3 | (21.00,22.00,23.00,24.00,25.00,26.00)
> (2 rows)
>
> users=#
>
>
> create type breakdown as (
> f1 numeric(9,2),
> f2 numeric(9,2),
> f3 numeric(9,2),
> f4 numeric(9,2),
> f5 numeric(9,2),
> f6 numeric(9,2)
> );
>
> create table sessions (
> ID int4 not null primary key,
> v1 numeric(9,2),
> v2 numeric(9,2),
> v3 numeric(9,2),
> v4 numeric(9,2),
> v5 numeric(9,2),
> v6 numeric(9,2),
> v7 numeric(9,2)
> );
> insert into sessions values
> (1,1,2,3,4,5,6,7),(2,11,12,13,14,15,16,17),(3,21,22,23,24,25,26,27);
>
> create or replace function do_breakdown(
> v1 numeric(9,2),
> v2 numeric(9,2),
> v3 numeric(9,2),
> v4 numeric(9,2),
> v5 numeric(9,2),
> v6 numeric(9,2),
> v7 numeric(9,2)
> ) returns breakdown as $$
> DECLARE
> D breakdown;
> BEGIN
> -- calculate breakdown
> D.f1=v1;
> D.f2=v2;
> D.f3=v3;
> D.f4=v4;
> D.f5=v5;
> D.f6=v6;
> return D;
> END;
> $$
> LANGUAGE PLPGSQL;
>
> create or replace function do_breakdown(vID int4) RETURNS breakdown
> AS $$
> DECLARE
> v RECORD;
> D breakdown;
> BEGIN
> IF vID IS NULL THEN RETURN NULL; END IF;
> select into v * from sessions s where s.ID = vID;
> IF NOT FOUND THEN
> RAISE NOTICE 'breakdown: % not found',vID;
> RETURN NULL;
> END IF;
> RETURN do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
> END;
> $$
> LANGUAGE PLPGSQL;
>
>
>
>
>
--
| Torsten Grust
| Torsten.Grust@gmail.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-18 11:20 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 14+ messages in thread
From: Gary Stainburn @ 2021-03-18 11:20 UTC (permalink / raw)
To: ; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On 18/03/2021 10:39, Torsten Grust wrote:
> Hi Gary,
>
> a shot in the dark but maybe
>
> SELECT id, (do_breakdown(id)).*
> FROM ...
>
>
> already does the job? (I'm on 13.2 here and my quickly whipped up
> example shows the desired behavior.)
>
> Best wishes,
> —Torsten
Perfect, did exactly what I wanted. I must have tried pretty much every
option except that one.
Thanks
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-18 14:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Torsten Grust <torsten.grust@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2021-03-18 14:28 UTC (permalink / raw)
To: Torsten Grust <torsten.grust@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>; Gary Stainburn <gary.stainburn@ringways.co.uk>
Torsten Grust <torsten.grust@gmail.com> writes:
> Hi Gary,
> a shot in the dark but maybe
> SELECT id, (do_breakdown(id)).*
> FROM ...
> already does the job?
Beware --- what that actually does is expand into
SELECT id, (do_breakdown(id)).f1, (do_breakdown(id)).f2, ...
so that the function will be invoked N times if it produces N columns.
What you generally want to do is invoke the function as a lateral FROM
item:
SELECT id, f.* FROM table AS t, LATERAL do_breakdown(t.id) AS f;
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-18 14:32 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Gary Stainburn @ 2021-03-18 14:32 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
On 18/03/2021 14:28, Tom Lane wrote:
> Beware --- what that actually does is expand into
> SELECT id, (do_breakdown(id)).f1, (do_breakdown(id)).f2, ...
>
> so that the function will be invoked N times if it produces N columns.
>
> What you generally want to do is invoke the function as a lateral FROM
> item:
>
> SELECT id, f.* FROM table AS t, LATERAL do_breakdown(t.id) AS f;
>
> regards, tom lane
Thanks for the info Tom, I can see how that would be quite a performance
hit, not to mention adverse effects if these functions start doing updates.
gary=# SELECT id, f.* FROM sessions AS t, LATERAL do_breakdown(t.id) AS f;
id | f1 | f2 | f3 | f4 | f5 | f6
----+-------+-------+-------+-------+-------+-------
1 | 1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 6.00
2 | 11.00 | 12.00 | 13.00 | 14.00 | 15.00 | 16.00
3 | 21.00 | 22.00 | 23.00 | 24.00 | 25.00 | 26.00
(3 rows)
gary=#
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-22 10:34 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 14+ messages in thread
From: Gary Stainburn @ 2021-03-22 10:34 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
I've added another function, partly to aid debugging, partly to test the
next part of the project.
The idea is simple. select the results of the calculation into a local
variable and then process it. However, I can't get the select to work.
The failure message relates to the "select into D" line.
gary=# select * from read_breakdown(1);
ERROR: invalid input syntax for type numeric:
"(1.00,2.00,3.00,4.00,5.00,6.00)"
CONTEXT: PL/pgSQL function read_breakdown(integer) line 12 at SQL statement
gary=#
create or replace function read_breakdown(vID int4) RETURNS breakdown
AS $$
DECLARE
v RECORD;
D breakdown;
BEGIN
IF vID IS NULL THEN RETURN NULL; END IF;
select into v * from sessions s where s.ID = vID;
IF NOT FOUND THEN
RAISE NOTICE 'breakdown: % not found',vID;
RETURN NULL;
END IF;
select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
IF NOT FOUND THEN
RAISE NOTICE 'breakdown: % calculation failed',vID;
RETURN NULL;
END IF;
RAISE NOTICE 'read_breakdown: f1=%',D.f1;
RAISE NOTICE 'read_breakdown: f2=%',D.f2;
RAISE NOTICE 'read_breakdown: f3=%',D.f3;
RAISE NOTICE 'read_breakdown: f4=%',D.f4;
RAISE NOTICE 'read_breakdown: f5=%',D.f5;
RAISE NOTICE 'read_breakdown: f6=%',D.f6;
RETURN D;
END;
$$
LANGUAGE PLPGSQL;
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-22 10:40 Pavel Stehule <pavel.stehule@gmail.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 14+ messages in thread
From: Pavel Stehule @ 2021-03-22 10:40 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi
po 22. 3. 2021 v 11:34 odesílatel Gary Stainburn <
gary.stainburn@ringways.co.uk> napsal:
> I've added another function, partly to aid debugging, partly to test the
> next part of the project.
>
> The idea is simple. select the results of the calculation into a local
> variable and then process it. However, I can't get the select to work.
> The failure message relates to the "select into D" line.
>
> gary=# select * from read_breakdown(1);
> ERROR: invalid input syntax for type numeric:
> "(1.00,2.00,3.00,4.00,5.00,6.00)"
> CONTEXT: PL/pgSQL function read_breakdown(integer) line 12 at SQL
> statement
> gary=#
>
>
> create or replace function read_breakdown(vID int4) RETURNS breakdown
> AS $$
> DECLARE
> v RECORD;
> D breakdown;
> BEGIN
> IF vID IS NULL THEN RETURN NULL; END IF;
> select into v * from sessions s where s.ID = vID;
> IF NOT FOUND THEN
> RAISE NOTICE 'breakdown: % not found',vID;
> RETURN NULL;
> END IF;
> select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
> IF NOT FOUND THEN
> RAISE NOTICE 'breakdown: % calculation failed',vID;
> RETURN NULL;
> END IF;
> RAISE NOTICE 'read_breakdown: f1=%',D.f1;
> RAISE NOTICE 'read_breakdown: f2=%',D.f2;
> RAISE NOTICE 'read_breakdown: f3=%',D.f3;
> RAISE NOTICE 'read_breakdown: f4=%',D.f4;
> RAISE NOTICE 'read_breakdown: f5=%',D.f5;
> RAISE NOTICE 'read_breakdown: f6=%',D.f6;
> RETURN D;
> END;
> $$
> LANGUAGE PLPGSQL;
>
>
select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
this line is wrong
you should to use select * from do_breakdown(..) into D
note - plpgsql is case insensitive language - all identifiers are
transformed to lower case form - so using identifiers like D is not a good
idea.
Regards
Pavel
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-22 10:54 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Gary Stainburn @ 2021-03-22 10:54 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
On 22/03/2021 10:40, Pavel Stehule wrote:
> select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
>
> this line is wrong
>
> you should to use select * from do_breakdown(..) into D
>
> note - plpgsql is case insensitive language - all identifiers are
> transformed to lower case form - so using identifiers like D is not a
> good idea.
>
> Regards
>
> Pavel
Hi Pavel,
Thank you for this. As soon as I read your answer, it was obvious. It
matches the same issue I had last week.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-22 10:56 Pavel Stehule <pavel.stehule@gmail.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 14+ messages in thread
From: Pavel Stehule @ 2021-03-22 10:56 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
po 22. 3. 2021 v 11:54 odesílatel Gary Stainburn <
gary.stainburn@ringways.co.uk> napsal:
> On 22/03/2021 10:40, Pavel Stehule wrote:
>
> select into D do_breakdown(v.v1,v.v2,v.v3,v.v4,v.v5,v.v6,v.v7);
>
> this line is wrong
>
> you should to use select * from do_breakdown(..) into D
>
> note - plpgsql is case insensitive language - all identifiers are
> transformed to lower case form - so using identifiers like D is not a good
> idea.
>
> Regards
>
> Pavel
>
> Hi Pavel,
>
> Thank you for this. As soon as I read your answer, it was obvious. It
> matches the same issue I had last week.
>
probably you can write
D := do_breakdown(...);
and it will be faster
Pavel
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-22 11:01 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Gary Stainburn @ 2021-03-22 11:01 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
On 22/03/2021 10:56, Pavel Stehule wrote:
> probably you can write
>
> D := do_breakdown(...);
>
> and it will be faster
>
> Pavel
That also worked, which surprised me as I thought I'd already Tried that.
Thanks.
Gary
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: select into composite type / return
@ 2021-03-22 11:15 Pavel Stehule <pavel.stehule@gmail.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 14+ messages in thread
From: Pavel Stehule @ 2021-03-22 11:15 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
po 22. 3. 2021 v 12:01 odesílatel Gary Stainburn <
gary.stainburn@ringways.co.uk> napsal:
> On 22/03/2021 10:56, Pavel Stehule wrote:
>
> probably you can write
>
> D := do_breakdown(...);
>
> and it will be faster
>
> Pavel
>
>
> That also worked, which surprised me as I thought I'd already Tried that.
>
this way is significantly faster. Use SELECT only a) when you read from
tables, b) when you work with an set of rows (using unnest function)
Pavel
> Thanks.
>
> Gary
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2021-03-22 11:15 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-17 16:48 select into composite type / return Gary Stainburn <gary.stainburn@ringways.co.uk>
2021-03-17 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-17 17:42 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2021-03-18 10:05 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2021-03-18 11:10 ` Torsten Grust <torsten.grust@gmail.com>
2021-03-18 14:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-18 14:32 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2021-03-22 10:34 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2021-03-22 10:40 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-03-22 10:54 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2021-03-22 10:56 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-03-22 11:01 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2021-03-22 11:15 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-03-18 11:20 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox