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!
