Thread: Substitute a variable in PL/PGSQL.

Substitute a variable in PL/PGSQL.

From

Steve Martin

Date:

22 July 2008, 15:42:12

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.

Re: Substitute a variable in PL/PGSQL.

From

"Francisco Reyes"

Date:

22 July 2008, 22:07:26

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?

Re: Substitute a variable in PL/PGSQL.

From

artacus@comcast.net

Date:

23 July 2008, 06:19:03

You can do it in straight sql like so.

SELECT (array[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10])[i]
FROM test t, generate_series(1,10) i

Art

Re: Substitute a variable in PL/PGSQL.

From

Klint Gore

Date:

23 July 2008, 08:10:37

Steve Martin wrote:
> 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?
>
>
> 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;
>
>
> Or is there another way other than using another procedural language.
>
> Thanks - Steve M.
>

There's no direct way to reference a particular field in a record
variable where the field name is held in a variable in pl/pgsql.
I.E. if ted = 'col1' there's no way to reference bob.ted to give you the
value of bob.col1.

If you want it easy to code but have to create something for every table
and modify it ever time the table changes

create view test_vertical_table as
select col1::text from test
union all
select col2::text from test
union all
select col3::text from test
union all
select col4::text from test
union all
select col5::text from test
...


If you want to go the generic function route

CREATE OR REPLACE FUNCTION testfunc(text) RETURNS SETOF text AS $$
DECLARE
    vertTableName alias for $1;
    ted text;
    bob RECORD;
    bill record;
BEGIN
    for bill in
        select table_name, column_name
        from information_schema.columns
        where table_schema = public
        and table_name = vertTableName
    loop
        FOR bob IN
            execute 'SELECT '||bill.column_name||' as thiscol FROM '||bill.table_name
        LOOP
            ted := bob.thiscol;
            RETURN NEXT ted;
        END LOOP;
    end loop;
    RETURN;
END
$$ LANGUAGE plpgsql;



klint.


--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

Re: Substitute a variable in PL/PGSQL.

From

"Merlin Moncure"

Date:

23 July 2008, 17:40:16

On Wed, Jul 23, 2008 at 4:08 AM, Klint Gore <kgore4@une.edu.au> wrote:
> Steve Martin wrote:
>>
>> 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?
>>
>>
>> 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;
>>
>>
>> Or is there another way other than using another procedural language.
>>
>> Thanks - Steve M.
>>
>
> There's no direct way to reference a particular field in a record variable
> where the field name is held in a variable in pl/pgsql.
> I.E. if ted = 'col1' there's no way to reference bob.ted to give you the
> value of bob.col1.
>
> If you want it easy to code but have to create something for every table and
> modify it ever time the table changes
>
> create view test_vertical_table as
> select col1::text from test
> union all
> select col2::text from test
> union all
> select col3::text from test
> union all
> select col4::text from test
> union all
> select col5::text from test
> ...
>
>
> If you want to go the generic function route
>
> CREATE OR REPLACE FUNCTION testfunc(text) RETURNS SETOF text AS $$
> DECLARE       vertTableName alias for $1;
>   ted text;
>   bob RECORD;
>   bill record;
> BEGIN
>   for bill in       select table_name, column_name       from
> information_schema.columns       where table_schema = public       and
> table_name = vertTableName
>   loop
>       FOR bob IN           execute 'SELECT '||bill.column_name||' as thiscol
> FROM '||bill.table_name       LOOP
>           ted := bob.thiscol;
>           RETURN NEXT ted;
>       END LOOP;
>   end loop;
>   RETURN;
> END
> $$ LANGUAGE plpgsql;

here is a way to do it with record variables...no inner loop but
doesn't the column names.  with a little work you could add those with
some queries to information_schema (i don't think it's worth it
though).

create or replace function ff(tablename text) returns setof text as
$$
  declare
    r record;
  begin
    for r in
      execute 'select record_out(' || tablename || ') as f' ||
        ' from ' || tablename loop
      return next r.f;
    end loop;
  end;
$$ language plpgsql;

merlin

Re: Substitute a variable in PL/PGSQL.

From

Steve Martin

Date:

25 July 2008, 12:48:53

Steve Martin 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.
>
> 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.

Found that this function works if I process by column.
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
    bob RECORD;
    ted TEXT;
    may TEXT;
BEGIN
    FOR i IN 1..10 LOOP
        ted := 'col' || i;
        may := ' SELECT ' || ted || ' as col FROM test';
        FOR bob IN EXECUTE may LOOP
            RETURN NEXT bob.col;
        END LOOP;
    END LOOP;
    RETURN;
END
$$ LANGUAGE plpgsql;

test=> select testfunc as data from testfunc() ;
 data
------
 a
 A
 1
 b
 B
 2
 c
 C
 3
 d
 D
 4
 e
 E
 5
 f
 F
 6
 g
 G
 7 d
 D
 4
 e
 E
 5
 f
 F
 6
 g
 G
 7
 h
 H
 8
 i
 I
 9
 j
 J
 10
(30 rows)

test=>

Any ideas on how to process by row?

Steve Martin

Re: Substitute a variable in PL/PGSQL.

From

Steve Martin

Date:

25 July 2008, 12:48:55

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

Re: Substitute a variable in PL/PGSQL.

From

Steve Martin

Date:

25 July 2008, 12:49:00

Merlin Moncure wrote:

>On Wed, Jul 23, 2008 at 4:08 AM, Klint Gore <kgore4@une.edu.au> wrote:
>
>
>here is a way to do it with record variables...no inner loop but
>doesn't the column names.  with a little work you could add those with
>some queries to information_schema (i don't think it's worth it
>though).
>
>create or replace function ff(tablename text) returns setof text as
>$$
>  declare
>    r record;
>  begin
>    for r in
>      execute 'select record_out(' || tablename || ') as f' ||
>        ' from ' || tablename loop
>      return next r.f;
>    end loop;
>  end;
>$$ language plpgsql;
>
>merlin
>
>
>
Hi Merlin,

Where can I find out more on the record_out function?

Steve M.

Re: Substitute a variable in PL/PGSQL.

From

"Roberts, Jon"

Date:

25 July 2008, 14:04:22

> 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.
>

FINALLY you get to the requirements.  Next time, just ask a question
like the above.  You were asking how to solve a technical problem that
didn't relate to the actual business need.

Here are three ways to skin this cat.

--version 1
select col_data from test2
except
select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') ||

       coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') ||

       coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') ||

       coalesce(col10, '')
  from test

--version 2
select col_data
  from test2 t2
 where not exists (select null
                     from test t
                    where t2.col_data = coalesce(t.col1, '') ||
                                        coalesce(t.col2, '') ||
                                        coalesce(t.col3, '') ||
                                        coalesce(t.col4, '') ||
                                        coalesce(t.col5, '') ||
                                        coalesce(t.col6, '') ||
                                        coalesce(t.col7, '') ||
                                        coalesce(t.col8, '') ||
                                        coalesce(t.col9, '') ||
                                        coalesce(t.col10, ''))
--version 3
select t2.col_data
  from test2 t2
  left join (select coalesce(col1, '') || coalesce(col2, '') ||
                    coalesce(col3, '') || coalesce(col4, '') ||
                    coalesce(col5, '') || coalesce(col6, '') ||
                    coalesce(col7, '') || coalesce(col8, '') ||
                    coalesce(col9, '') || coalesce(col10, '') as
col_data
               from test) t
    on t2.col_data = t.col_data
 where t.col_data is null


Jon

Re: Substitute a variable in PL/PGSQL.

From

Steve Martin

Date:

28 July 2008, 01:03:15

Roberts, Jon wrote:

>>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.
>>
>>
>>
>
>FINALLY you get to the requirements.  Next time, just ask a question
>like the above.  You were asking how to solve a technical problem that
>didn't relate to the actual business need.
>
>Here are three ways to skin this cat.
>
>--version 1
>select col_data from test2
>except
>select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') ||
>
>       coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') ||
>
>       coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') ||
>
>       coalesce(col10, '')
>  from test
>
>--version 2
>select col_data
>  from test2 t2
> where not exists (select null
>                     from test t
>                    where t2.col_data = coalesce(t.col1, '') ||
>                                        coalesce(t.col2, '') ||
>                                        coalesce(t.col3, '') ||
>                                        coalesce(t.col4, '') ||
>                                        coalesce(t.col5, '') ||
>                                        coalesce(t.col6, '') ||
>                                        coalesce(t.col7, '') ||
>                                        coalesce(t.col8, '') ||
>                                        coalesce(t.col9, '') ||
>                                        coalesce(t.col10, ''))
>--version 3
>select t2.col_data
>  from test2 t2
>  left join (select coalesce(col1, '') || coalesce(col2, '') ||
>                    coalesce(col3, '') || coalesce(col4, '') ||
>                    coalesce(col5, '') || coalesce(col6, '') ||
>                    coalesce(col7, '') || coalesce(col8, '') ||
>                    coalesce(col9, '') || coalesce(col10, '') as
>col_data
>               from test) t
>    on t2.col_data = t.col_data
> where t.col_data is null
>
>
>Jon
>
>
Thanks Jon for the hints.
Steve

Re: Substitute a variable in PL/PGSQL.

From

Steve Martin

Date:

28 July 2008, 01:09:33

Klint Gore wrote:

> Steve Martin wrote:
>
>> 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?
>>
>>
>> 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;
>>
>>
>> Or is there another way other than using another procedural language.
>>
>> Thanks - Steve M.
>>
>
>
> There's no direct way to reference a particular field in a record
> variable where the field name is held in a variable in pl/pgsql.
> I.E. if ted = 'col1' there's no way to reference bob.ted to give you
> the value of bob.col1.
>
> If you want it easy to code but have to create something for every
> table and modify it ever time the table changes
>
> create view test_vertical_table as
> select col1::text from test
> union all
> select col2::text from test
> union all
> select col3::text from test
> union all
> select col4::text from test
> union all
> select col5::text from test
> ...
>
>
> If you want to go the generic function route
>
> CREATE OR REPLACE FUNCTION testfunc(text) RETURNS SETOF text AS $$
> DECLARE        vertTableName alias for $1;
>    ted text;
>    bob RECORD;
>    bill record;
> BEGIN
>    for bill in        select table_name, column_name        from
> information_schema.columns        where table_schema = public
> and table_name = vertTableName
>    loop
>        FOR bob IN            execute 'SELECT '||bill.column_name||' as
> thiscol FROM '||bill.table_name        LOOP
>            ted := bob.thiscol;
>            RETURN NEXT ted;
>        END LOOP;
>    end loop;
>    RETURN;
> END
> $$ LANGUAGE plpgsql;
>
>
>
> klint.
>
Hi Klint,
Thanks for the advice, I found the sql to get the column names useful.
Steve M.

Re: Substitute a variable in PL/PGSQL.

From

Steve Martin

Date:

28 July 2008, 01:11:36

artacus@comcast.net wrote:

>You can do it in straight sql like so.
>
>SELECT (array[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10])[i]
>FROM test t, generate_series(1,10) i
>
>Art
>
>
>
Hi Art,
Thanks for the advice, in my case using arrays was not a option as the
data could be null.
Steve M.