Ad hoc SETOF type definition?
Pg 9.6.24 (Yes, I know it's EOL.)
This simple "programming example" function works perfectly. However, it
requires me to create the TYPE "foo".
CREATE TYPE foo AS (tab_name TEXT, num_pages INT);
CREATE FUNCTION dba.blarge()
RETURNS SETOF foo
LANGUAGE plpgsql
AS
$$
DECLARE
ret foo;
bar CURSOR FOR
select relname::text as table_name, relpages
from pg_class where relkind = 'r'
order by 1;
BEGIN
FOR i IN bar LOOP
SELECT i.table_name, i.relpages INTO ret;
RETURN NEXT ret;
END LOOP;
END;
$$;
Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?
--
Born in Arizona, moved to Babylonia.
Ron <ronljohnsonjr@gmail.com> writes:
Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?
Doesn't RETURNS TABLE meet the need already?
regards, tom lane
On 9/26/23 12:46, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?Doesn't RETURNS TABLE meet the need already?
That rationale means that RETURN SETOF is not needed, and can be removed
from Pg, since "RETURNS TABLE meet the need already".
But of course there /are/ times when RETURN SETOF /is/ useful.
So... can ad hoc SETOF definitions be created in the function definition, or
is CREATE TYPE the only way to do it?
--
Born in Arizona, moved to Babylonia.
On 9/26/23 11:03 AM, Ron wrote:
On 9/26/23 12:46, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?Doesn't RETURNS TABLE meet the need already?
That rationale means that RETURN SETOF is not needed, and can be
removed from Pg, since "RETURNS TABLE meet the need already".But of course there /are/ times when RETURN SETOF /is/ useful.
So... can ad hoc SETOF definitions be created in the function
definition, or is CREATE TYPE the only way to do it?
That is what RETURNS TABLE does:
https://www.postgresql.org/docs/current/sql-createfunction.html
"/|column_name|/
The name of an output column in the |RETURNS TABLE| syntax. This is
effectively another way of declaring a named |OUT| parameter, except
that |RETURNS TABLE| also implies |RETURNS SETOF|.
"
Show quoted text
--
Born in Arizona, moved to Babylonia.
Ron <ronljohnsonjr@gmail.com> writes:
On 9/26/23 12:46, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?
Doesn't RETURNS TABLE meet the need already?
That rationale means that RETURN SETOF is not needed, and can be removed
from Pg, since "RETURNS TABLE meet the need already".
Indeed, we might not have invented SETOF if RETURNS TABLE were there
first ... but it wasn't. SETOF is from PostQUEL originally I think.
RETURNS TABLE is from some johnny-come-lately addition to the SQL spec.
We're not going to remove SETOF at this point.
So... can ad hoc SETOF definitions be created in the function definition, or
is CREATE TYPE the only way to do it?
I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row. Admittedly, what you get is an anonymous record type and not
a named composite type, but if you want to name the type then I think
having to issue an explicit CREATE TYPE is a good thing. That makes
it clear that the type exists independently of the function. (Our
behavior of automatically making composite types for tables seems to
me to have been a rather unfortunate choice.)
regards, tom lane
On 9/26/23 13:15, Tom Lane wrote:
Ron <ronljohnsonjr@gmail.com> writes:
On 9/26/23 12:46, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?Doesn't RETURNS TABLE meet the need already?
That rationale means that RETURN SETOF is not needed, and can be removed
from Pg, since "RETURNS TABLE meet the need already".Indeed, we might not have invented SETOF if RETURNS TABLE were there
first ... but it wasn't. SETOF is from PostQUEL originally I think.
RETURNS TABLE is from some johnny-come-lately addition to the SQL spec.
We're not going to remove SETOF at this point.So... can ad hoc SETOF definitions be created in the function definition, or
is CREATE TYPE the only way to do it?I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row.
There might be some other logic in the body of the FOR loop that is not
practical to embed in the body of the SELECT statement.
Admittedly, what you get is an anonymous record type and not
a named composite type, but if you want to name the type then I think
having to issue an explicit CREATE TYPE is a good thing. That makes
it clear that the type exists independently of the function.
If you're going to only use that type with the function, then an anonymous
record type is Good Enough.
Just like anonymous DO blocks are useful.
(Our
behavior of automatically making composite types for tables seems to
me to have been a rather unfortunate choice.)
It makes developers/DBAs lives just that much easier.
--
Born in Arizona, moved to Babylonia.
On 9/26/23 12:30, Ron wrote:
On 9/26/23 13:15, Tom Lane wrote:
Ron <ronljohnsonjr@gmail.com> writes:
On 9/26/23 12:46, Tom Lane wrote:
I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row.There might be some other logic in the body of the FOR loop that is not
practical to embed in the body of the SELECT statement.
I think you are conflating RETURNS TABLE and RETURN QUERY. You can build
a 'TABLE' from variables outside of a query.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 9/26/23 13:29, Adrian Klaver wrote:
On 9/26/23 12:30, Ron wrote:
On 9/26/23 13:15, Tom Lane wrote:
Ron <ronljohnsonjr@gmail.com> writes:
On 9/26/23 12:46, Tom Lane wrote:
I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row.There might be some other logic in the body of the FOR loop that is
not practical to embed in the body of the SELECT statement.I think you are conflating RETURNS TABLE and RETURN QUERY. You can build
a 'TABLE' from variables outside of a query.
As a very simple example:
create table source(id integer, fld_1 varchar);
insert into source values (1, 'cat'), (2, 'dog'), (3, 'fish');
CREATE OR REPLACE FUNCTION public.table_return(multiplier integer,
suffix character varying)
RETURNS TABLE(multiplied integer, fld_suffix character varying,
rand_number numeric)
LANGUAGE plpgsql
AS $function$
DECLARE
_id integer;
_fld varchar;
BEGIN
FOR _id, _fld IN
SELECT
id, fld_1
FROM
source
LOOP
multiplied = _id * multiplier;
fld_suffix = _fld || '_' || suffix;
rand_number = random() * 100;
RETURN NEXT;
END LOOP;
END;
$function$
;
select * from table_return(2, 'test');
multiplied | fld_suffix | rand_number
------------+------------+------------------
2 | cat_test | 79.7745033326483
4 | dog_test | 12.5713231966519
6 | fish_test | 3.21770069680842
--
Adrian Klaver
adrian.klaver@aklaver.com
On 9/26/23 16:29, Adrian Klaver wrote:
[snip]
As a very simple example:
This is EXACTLY what I was looking for. Thank you.
create table source(id integer, fld_1 varchar);
insert into source values (1, 'cat'), (2, 'dog'), (3, 'fish');
CREATE OR REPLACE FUNCTION public.table_return(multiplier integer, suffix
character varying)
RETURNS TABLE(multiplied integer, fld_suffix character varying,
rand_number numeric)
LANGUAGE plpgsql
AS $function$
DECLARE
_id integer;
_fld varchar;
BEGINFOR _id, _fld IN
SELECT
id, fld_1
FROM
source
LOOP
multiplied = _id * multiplier;
fld_suffix = _fld || '_' || suffix;
rand_number = random() * 100;RETURN NEXT;
END LOOP;END;
$function$
;select * from table_return(2, 'test');
multiplied | fld_suffix | rand_number
------------+------------+------------------
2 | cat_test | 79.7745033326483
4 | dog_test | 12.5713231966519
6 | fish_test | 3.21770069680842
--
Born in Arizona, moved to Babylonia.
On Tue, Sep 26, 2023 at 1:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote
it clear that the type exists independently of the function. (Our
behavior of automatically making composite types for tables seems to
me to have been a rather unfortunate choice.)
I really like this behavior and exploit it heavily, in order to,
*) use tables as variable inputs to functions
*) record marshalling, populate_record, etc
*) type safe variable declaration in plpgsql functions
*) arrays of table type for temporary storage (jsonb is eating into this
case though)
*) dblink replication tricks to migrate data across the wire (baroque with
fdw, but still useful in ad hoc coding)
Granted, from the classic sql programming perspective, this is all
highly exotic and weird. There is an organic beauty though in deep sql or
plpgsql coding that comes out and a lot of it is from the type system :).
In fact, I find the concept that 'tables are types' (which I think you are
implying should ideally not be the case by default) is so brilliant and
profound that it is really what sets postgresql apart from competitive
offerings. Granted, you can do all of the same things with composite
types, json, etc, but tables often do the job handily and safely with less
programming effort and the type naturally extends with the table refinement
over time.
I find that the opposite case, basically, to create composite types is
increasingly rare in practice, with jsonb handling transient and unsafe
cases, and 'table created types' covering most of the rest. A lot of it
comes down to style I guess.
merlin