pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: dinocherian@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Re: How to return more than one row of data from a function in PL/pgSQL
Date: Fri, 23 Nov 2001 02:29:25 -0500
Message-ID: <E167Am8-0003kV-00@xyzzy.lan.internal> (raw)
In-Reply-To: <e15d0e91.0111190122.e4c2975@posting.google.com>
References: <e15d0e91.0111190122.e4c2975@posting.google.com>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 19 04:22 am, dino ck wrote:
> Hi,
>
> Is there a way to return more than one row of data from a function in
> PL/pgSQL?
>
> Anybody please help me with an example or a good resource on the net.

I don't know exactly what you're trying to achieve, BUT, you might want to try
a temporary table or even something like this...

- -- initialize
DROP SEQUENCE multi_marker; DROP TABLE multi_results, foo; DROP FUNCTION multi_return(text);
- -- create
CREATE TABLE foo(data TEXT NOT NULL);
CREATE SEQUENCE multi_marker;
CREATE TABLE multi_results(r_id INTEGER NOT NULL, data TEXT NOT NULL);
CREATE FUNCTION multi_return(text) RETURNS INTEGER AS '
  DECLARE r_idx INTEGER;
    ins_1 CONSTANT TEXT := ''INSERT INTO multi_results (r_id, data) SELECT '';
    ins_2 CONSTANT TEXT:= '', data || ''''_add'''' FROM foo WHERE '';
    where_clause ALIAS FOR $1;
    ins_final TEXT;
  BEGIN  r_idx := nextval(''multi_marker'');
    ins_final := ins_1 || r_idx || ins_2 || where_clause;
    RAISE NOTICE ''executing: %'', ins_final;
    EXECUTE ins_final;
    RETURN r_idx;
  END;' LANGUAGE 'plpgsql';
- -- populate
INSERT INTO foo VALUES (''); INSERT INTO foo VALUES ('a'); INSERT INTO foo VALUES ('b');
- -- usage
BEGIN;
SELECT multi_return('length(data) > 0'::text);   -- returns an index, ie 1
SELECT data FROM multi_results WHERE r_id = 1;         -- get results
DELETE FROM multi_results WHERE r_id = 1;             -- cleanup.
COMMIT;

- -- 
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjv9+tUACgkQCT73CrRXhLHGDACeMgpWfE8O1fHOkO7kFuNLNDvd
7XoAn10pv/9enQ9NyetvUp5s32iP3uO8
=57Z4
-----END PGP SIGNATURE-----



view thread (5+ messages)  latest in thread

Message-ID: <E167Am8-0003kV-00@xyzzy.lan.internal>
Permalink:  ../E167Am8-0003kV-00@xyzzy.lan.internal/
Also on:    postgresql.org/message-id/E167Am8-0003kV-00@xyzzy.lan.internal

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: drew@xyzzy.dhs.org, dinocherian@yahoo.com
  Subject: Re: How to return more than one row of data from a function in PL/pgSQL
  In-Reply-To: <E167Am8-0003kV-00@xyzzy.lan.internal>

* 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