CODEWITHSUNDEEP
functionpostgresqlplpgsqlexists
Sameera
Sameera

Reputation: 304

PL/pgSQL function is not working properly

I am writing a function to create tables for a given table count. Before creating a particular table, I want to check whether that table already exists:

create or replace function test (numoftables int) returns void as $$
    declare
    i integer;
    begin 
    i:=0;
    while i< numoftables loop
        if not exists (select * from table$i$)  -- check if table exists
        then 
            create table table$i$(
                column1 int,
                column2 int,
                column1 text,
                column2 text,
                column3 text); 
        end if;
    end loop;
end;

$$
language 'plpgsql';

When I ran this code it gave me an error.

ERROR:  relation "table$i$" does not exist
LINE 1: SELECT not exists (select * from table$i$)

Can anyone tell me how can I change this code to work properly.

Upvotes: 0

Views: 119

Answers (1)

roman
roman

Reputation: 117485

  • You can't check table existence like this. Use pg_class system table to check if table exists
  • You have to increment i inside your function, or you'll end up with infinite loop
create or replace function test (numoftables int) returns void as $$
    declare
    i integer;
    begin 
    i:=0;
    while i< numoftables loop
        if not exists (select * from pg_class where relname = 'table' || i::text)
        then
            execute '
            create table table' || i::text || '(
                column1 int,
                column2 int,
                column3 text,
                column4 text,
                column5 text);
            ';
        end if;
        i := i + 1;
    end loop;
end;

$$
language 'plpgsql';

I've tried not changed your logic or syntax much, so it's still your function.

sql fiddle demo

Upvotes: 1

Related Questions