Ad hoc SETOF type definition?

From: Ron <ronljohnsonjr(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Ad hoc SETOF type definition?
Date: 2023-09-26 17:41:05
Message-ID: 9707e21e-eed3-3c06-821e-e935eab4c098@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2023-09-26 17:46:26 Re: Ad hoc SETOF type definition?
Previous Message pgchem pgchem 2023-09-26 13:00:47 Is the logfile the only place to find the finish LSN?