CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

table name as variable in postgresql

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?

Upvotes: 6

Views: 4244

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 6

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

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

Upvotes: 2

Related Questions