Re: Substitute a variable in PL/PGSQL.

Hi Francisco,

Francisco Reyes wrote:

>On 12:33 am 07/22/08 Steve Martin <steve.martin@nec.co.nz> wrote:
>
>
>>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.
>>
>>
>
>Are you trying to do a generic function that would work for any table or
>for just a single table?
>
>Is it goint to run against a large data set?
>
>
>
What I am trying to do is find the difference between two tables, one
that stores the
information in a single column, and the other which stores the same data
in multiple
columns.

E.g.
CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text,
col6 text, col7 text, col8 text, col9 text, col10 text);
CREATE TABLE test2(col_data text NOT NULL,  some_data  text NOT NULL,
other_data text,
                                      CONSTRAINT test2_index PRIMARY KEY(
                                           col_data,
                                           some_data ));

Trying to find data set in test2.col_data that is not in test.col1 to
test.col10.

The data sets are very small, e.g. < 10 000 rows.

Using pl/pgsql. the tried using the pl/pgsql's EXECUTE statement,
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
    ted text;
    bob RECORD;
BEGIN
    FOR bob IN SELECT * FROM test LOOP
        FOR i IN 1..10 LOOP
            ted := 'bob.col' || i;
            EXECUTE 'RETURN NEXT ' || ted;
            -- RETURN NEXT bob.col1;
        END LOOP;
    END LOOP;
    RETURN;
END
$$ LANGUAGE plpgsql;

test=> select * from testfunc() ;
ERROR:  syntax error at or near "RETURN" at character 1
QUERY:  RETURN NEXT bob.col1
CONTEXT:  PL/pgSQL function "testfunc" line 8 at execute statement
LINE 1: RETURN NEXT bob.col1
        ^
test=>


Note Postgres version 8.1.10.

Regards
Steve Martin