Substitute a variable in PL/PGSQL.

From: Steve Martin <steve(dot)martin(at)nec(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: Substitute a variable in PL/PGSQL.
Date: 2008-07-22 04:33:46
Message-ID: 4885632A.4020307@nec.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I am trying to create a PL/PGSQL function to return the values of the
fields in a record, e.g. 1 value per row in the output of the function.

How do you substitute a variable?

Test case:

CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text,
col6 text, col7 text, col8 text, col9 text, col10 text);
INSERT INTO test VALUES ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j');
INSERT INTO test VALUES ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J');
INSERT INTO test VALUES ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10');

CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
ted varchar;
bob RECORD;
BEGIN
FOR bob IN SELECT * FROM test LOOP
FOR i IN 1..10 LOOP
ted := 'bob.col' || i;
RETURN NEXT ted;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;

test=> select * from testfunc();
testfunc
-----------
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
(30 rows)

test=>

Or:
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
bob RECORD;
ted TEXT;
BEGIN
FOR i IN 1..10 LOOP
ted := 'col' || i;
FOR bob IN SELECT ted FROM test LOOP
RETURN NEXT bob;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test=> select * from testfunc();
testfunc
----------
(col1)
(col1)
(col1)
(col2)
(col2)
(col2)
(col3)
(col3)
(col3)
(col4)
(col4)
(col4)
(col5)
(col5)
(col5)
(col6)
(col6)
(col6)
(col7)
(col7)
(col7)
(col8)
(col8)
(col8)
(col9)
(col9)
(col9)
(col10)
(col10)
(col10)
(30 rows)

test=>

Or is there another way other than using another procedural language.

Thanks - Steve M.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Giovanni Nervi 2008-07-22 07:39:12 Drop Index and Create Index
Previous Message Scott Marlowe 2008-07-22 02:05:56 Re: ERROR: could not open relation with OID 49152