current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
up vote 2 down vote favorite
1

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

share|improve this question
up vote 2 down vote accepted

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

share|improve this answer
    
That's pretty cool. Bonus points if you know how to do this by specifying the table as a string or object? For instance what if I wanted to run this on several different kinds of tables? – Payson Welch Jun 10 '15 at 18:22
    
@PaysonWelch - see edited answer. – klin Jun 10 '15 at 20:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.