PostgreSQL – How to Put Query Results into a Variable

join;plpgsqlpostgresql

I have written a Postgres anonymous block. In which I have written join query on multiple tables like:

select a.portal.*, a.p_fb_config.*, a.p_gplus_config  
from a.portal 
   left outer join a.p_fb_config on a.portal.pid = a.p_fb_config.pid 
   left outer join a.p_gplus_config on a.portal.pid = a.p_gplus_config.pid;

Now I want to catch this value in variable. So how should I declare that variable?

I need something like:

         portal_row a.portal%ROWTYPE;//for 3 tables

Best Answer

I assumed you were using a pl/pgsql block (probably a function).

For the generic case, you can use record, it can basically take a row of any structure, often used like

DECLARE
    i record;
BEGIN
    FOR i IN SELECT this, that, something, else FROM some_table
    LOOP
        ...
    END LOOP;

There is also a possibility of defining a view:

CREATE VIEW v_portal_data_with_config AS
select a.portal.*, a.p_fb_config.*, a.p_gplus_config  
-- note that this does not work straight away, there will be a column naming collision, 
-- so you'll have to add column aliases (which then makes using * impossible)
from a.portal 
   left outer join a.p_fb_config on portal.pid = p_fb_config.pid 
   left outer join a.p_gplus_config on portal.pid = p_gplus_config.pid;

Having this, you can then 

DECLARE
    i v_portal_data_with_config%ROWTYPE;
BEGIN
    SELECT * FROM v_portal_data_with_config WHERE ... ;

Sometimes, when having the view makes sense anyway (the query appears in multiple functions, for example), this may me a more convenient way. However, you introduce an additional dependency which makes harder to change the logic.

Related Question