Im trying to create a function and i just cant get it right.
So I've made a "basic" function which works:
CREATE FUNCTION test(integer) RETURNS integer AS'
DECLARE
input integer;
tablename text;
input_plus integer;
-- testtable_cursor CURSOR FOR SELECT id FROM TESTTABLE;
my_id integer;
rec RECORD;
my_nextval text;
BEGIN
input := $1;
tablename := input + 1;
EXECUTE '' CREATE TABLE function_table_'' || tablename || ''
(
new_id_'' || tablename || '' SERIAL not null primary key,
col3 int)'';
INSERT INTO function_table_2 (new_id_2,col3) values
(nextval(''function_table_2_new_id_2_seq''),5);
RETURN input;
END;
'language 'plpgsql'
Now what im trying to do is that the fuction will be able to make a custom
table depending on the input you give the function. That part works.
Consequently, the insert into statement has to be flexible as well but some
how that doesnt work.
The insert statement im trying to make looks like this:
INSERT INTO function_table_ || tablenname || values (new_id_ || tablename
||,col3) values (nextval(''function_table_ || tablename ||_new_id_ ||
tablename || _seq''),5);
The error i get:
NOTICE: CREATE TABLE will create implicit sequence
"function_table_2_new_id_2_seq" for "serial" column
"function_table_2.new_id_2"
CONTEXT: PL/pgSQL function "test" line 16 at execute statement
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"function_table_2_pkey" for table "function_table_2"
CONTEXT: PL/pgSQL function "test" line 16 at execute statement
ERROR: syntax error at or near "||" at character 29
CONTEXT: PL/pgSQL function "test" line 21 at SQL statement
Testtable and inserts:
CREATE TABLE testtable
(
id SERIAL not null primary key,
col1 int)
INSERT INTO testtable values (nextval('testtable_id_seq'),2);
INSERT INTO testtable values (nextval('testtable_id_seq'),3);
Can somebody enlighten me what im doing wrong conceirning the insert
statement (or anything else).
Thx in advance
Ed