table name as variable in postgresql

Question

I have a function as follows:

CREATE OR REPLACE FUNCTION func(a integer)
  RETURNS integer AS
$BODY$
begin

     for row in
                     Select id  from table_a    where quantity=1
    loop
        do something
    end loop

end;
$BODY$
  LANGUAGE plpgsql VOLATILE

I need to change this function to take another parameter which tells if to use table_a or table_b.

when whichtouse=1 I need to use table_a.

when whichtouse=2 I need to use table_b.

CREATE OR REPLACE FUNCTION func(a integer,whichtouse integer)
  RETURNS integer AS
$BODY$
begin

     for row in
                     Select id  from ??? where quantity=1
    loop
        do something
    end loop

end;
$BODY$
  LANGUAGE plpgsql VOLATILE

How can I determin which table to use?

user330315 · Accepted Answer · 2016-02-04 08:53:23Z

6

Use dynamic SQL:

CREATE OR REPLACE FUNCTION func(a integer, whichtouse integer)
  RETURNS integer AS
$BODY$
declare
  l_sql text;
  l_rec record;
begin
  if whichtouse = 1 then
    l_sql := format('select id from %I where qantity=1', 'table_a');
  else 
    l_sql := format('select id from %I where qantity=1', 'table_b');
  end if;

   for l_rec in execute l_sql
   loop
      -- do something
   end loop;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

answered Feb 4, 2016 at 8:53

user330315

Sign up to request clarification or add additional context in comments.

Comments

Abhishek · Accepted Answer · 2016-02-04 08:49:16Z

2

Using temporary table to get data from table based on condition.

CREATE OR REPLACE FUNCTION func(a integer,whichtouse integer)
RETURNS integer AS
$BODY$
DECLARE
    rec   record;
begin


    drop  table if exists temp_data;
    if whichtouse=1
    then 
        create temporary table temp_data as
        Select id  from table_a    where quantity=1;
    else
        create temporary table temp_data as
        Select id  from table_b    where quantity=1;
    end if;

    for rec in Select id  from temp_data 
    loop
        -- do something here
    end loop;

end;
$BODY$
LANGUAGE plpgsql VOLATILE

edited Feb 4, 2016 at 8:49

answered Feb 4, 2016 at 8:44

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

1 Comment

user330315 Over a year ago

Creating a temporary table is not necessary

Collectives™ on Stack Overflow

table name as variable in postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related