pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Ad hoc SETOF type definition?
Date: Tue, 26 Sep 2023 12:41:05 -0500
Message-ID: <9707e21e-eed3-3c06-821e-e935eab4c098@gmail.com> (raw)

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.





view thread (10+ messages)  latest in thread

Message-ID: <9707e21e-eed3-3c06-821e-e935eab4c098@gmail.com>
Permalink:  ../9707e21e-eed3-3c06-821e-e935eab4c098@gmail.com/
Also on:    postgresql.org/message-id/9707e21e-eed3-3c06-821e-e935eab4c098@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Ad hoc SETOF type definition?
  In-Reply-To: <9707e21e-eed3-3c06-821e-e935eab4c098@gmail.com>
  References: <9707e21e-eed3-3c06-821e-e935eab4c098@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox