Re: plpgsql syntax error

On Monday, October 10, 2011 3:35:27 am József Kurucz wrote:
> 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

I ran the function here and did not receive a syntax error. The problem though,
was it created a table named tblname. To get it to work as designed I
substituted :

create table tblname
(

);

with:

EXECUTE 'create table ' || tblname || '()';


> Thanks!

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com