Postgres Pro
Downloads
Home   >   mailing lists

Thread: Ad hoc SETOF type definition?

Ad hoc SETOF type definition?

From
Ron
Date:
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.

Re: Ad hoc SETOF type definition?

From
Tom Lane
Date:
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

Re: Ad hoc SETOF type definition?

From
Ron
Date:
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.

Re: Ad hoc SETOF type definition?

From
Adrian Klaver
Date:


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.

"

--
Born in Arizona, moved to Babylonia.

Re: Ad hoc SETOF type definition?

From
Tom Lane
Date:
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

Re: Ad hoc SETOF type definition?

From
Ron
Date:
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.

Re: Ad hoc SETOF type definition?

From
Adrian Klaver
Date:
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

Re: Ad hoc SETOF type definition?

From
Adrian Klaver
Date:
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

Re: Ad hoc SETOF type definition?

From
Ron
Date:
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;
> 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

-- 
Born in Arizona, moved to Babylonia.

Re: Ad hoc SETOF type definition?

From
Merlin Moncure
Date:
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