1

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
  • You mean as in OUTER JOIN(ing) tables b c d to table a? – Nelz Feb 25 '15 at 12:03
  • What do you want for the outcome - example? – Simo Kivistö Feb 25 '15 at 12:17
  • Yes, please give us some data of tables ( name of collumns ) . – Racer SQL Feb 25 '15 at 12:18
  • I have edited my question. – user3526905 Feb 25 '15 at 13:38
  • 2
    You have changed your question completely... However, a record could take all the values you select in the query. – dezso Feb 25 '15 at 14:07
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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