plpgsql syntax error

From: József Kurucz <jozsef(dot)kurucz(at)invitel(dot)hu>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql syntax error
Date: 2011-10-10 10:35:27
Message-ID: CAGJF9eFaiYq75+n15Trj62NAemYhOw6jaC3SjC852apLq+MYjg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm beginner in postgresql and plpgsql and i have the following problem.
I'm trying to check a table if exists or not and when not exists then
create it, but I get a syntax error.

Here is my code:

create or replace function check_table() returns void as
$$

DECLARE

mmonth integer;
yyear integer;

tblname varchar(30);

begin

SELECT into mmonth EXTRACT(MONTH FROM TIMESTAMP 'now' - INTERVAL '1 MONTH');
SELECT into yyear EXTRACT(YEAR FROM TIMESTAMP 'now' - INTERVAL '1 MONTH');

tblname := 'tbl_' || yyear || mmonth;

if not exists(select * from information_schema.tables
where
table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA
and table_name = tblname) then

create table tblname
(

);

end if;

end;
$$
language 'plpgsql';

But I get the following error:

ERROR: syntax error at or near "$1"
LINE 1: create table $1 ( )
^
QUERY: create table $1 ( )
CONTEXT: SQL statement in PL/PgSQL function "check_table" near line 22

Thanks!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Harshitha S 2011-10-10 10:41:04 Re: could not create file "base/16384/11500": File exists
Previous Message Harshitha S 2011-10-10 10:31:42 Re: could not create file "base/16384/11500": File exists