Обсуждение: plpgsql syntax error

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!

Hi,

On 10 October 2011 21:35, József Kurucz <jozsef.kurucz@invitel.hu> wrote:
> 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 think you have to use "execute":

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

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)

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.klaver@gmail.com

Many Thanks!!! It works!

Regards,

Josef

2011/10/10 Ondrej Ivanič <ondrej.ivanic@gmail.com>:
> Hi,
>
> On 10 October 2011 21:35, József Kurucz <jozsef.kurucz@invitel.hu> wrote:
>> 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 think you have to use "execute":
>
> execute 'create table ' || tblname || ' ()';
>
> http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> --
> Ondrej Ivanic
> (ondrej.ivanic@gmail.com)
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>