Reference Local Variable In PL/PGSQL Dynamic SQL Inside Function

Question

I have a PL/PGSQL function which is for data processing. I need to first select each row from a table, and then retrieve the column names and the associated values of each column. So basically I am un-pivoting the records to a horizontal state. This is necessary since they will be going into a key/value store instead of being stored horizontally.

Here is an abstract of the function I have so far:

CREATE OR REPLACE FUNCTION myfunc()
    RETURNS INT AS 
    $BODY$
DECLARE 
x record; 
aesql varchar;
aeval varchar;
y information_schema.columns%rowtype;
BEGIN
    FOR x IN
    SELECT * FROM mytable
    LOOP
        FOR y in
        SELECT * FROM information_schema.columns where table_schema = 'public' AND table_name = 'mytable'
        loop                                
            execute 'select cast(x.'||y.column_name||' as varchar) into aeval';
        end loop;
        -- add processing for aeval once the dynamic sql is figured out                     
    END LOOP;   
    RETURN 1;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

I have troubleshot this far enough that my understanding is that to do an execute statement it should be a CRUD query or something similar. Any queries where I tried to do a straight assignment like

execute 'aeval := x.'||y.column_name;

failed in syntax errors for 'aeval' or ':' if I was using ':aeval' etc.

So does anyone know if this is possible and how I might go about performing this dynamic sql? To sum it up I need to grab the value of the record x but I only know the column name.

When I try to run the function I receive the error:

ERROR: missing FROM-clause entry for table "x" Where: PL/pgSQL function myfunc() line 23 at EXECUTE statement

Answer 1

This funny query:

select
    translate(string_to_array(mytable.*::text,',')::text,'()','')::text[]
from mytable;

returns rows from mytable as text arrays. It will be much easier to loop over arrays in your function:

create or replace function myfunc()
returns setof text language plpgsql
as $$
declare
    eaval text;
    x text[];
begin
    for x in
        select translate(string_to_array(mytable.*::text,',')::text,'()','')::text[] 
        from mytable
    loop
        foreach eaval in array x loop
            return next eaval;
        end loop;
        return next '-- next row --';
    end loop;
end $$;

select * from myfunc();

---

The function with an argument - table name:

create or replace function myfunc(table_name text)
returns setof text language plpgsql
as $$
declare
    eaval text;
    x text[];
begin
    for x in
        execute format($fmt$
            select translate(string_to_array(%s.*::text,',')::text,'()','')::text[] 
            from %s 
            $fmt$, 
            table_name, table_name)
    loop
        foreach eaval in array x loop
            return next eaval;
        end loop;
        return next '-- next row --';
    end loop;
end $$;

select * from myfunc('mytable');
select * from myfunc('myschema.myanothertable');

Read more: 39.5.4. Executing Dynamic Commands and 9.4.1. format