Thread: plpgsql Functions: Text Variables
Hi, I want to create a function in plpgsql that must execute a INSERT query on the database. The problem comes in I want to create the query inside the function. Look at the following: CREATE OR REPLACE FUNCTION Test_Function(integer) RETURNS VOID AS $$ DECLARE table_name text := 'My_Table_Name'; BEGIN INSERT INTO "table_name" VALUES ($1); END $$ LANGUAGE plpgsql; When I try to create the function I get the following response from Postgres: ERROR: syntax error at or near "$1" LINE 1: INSERT INTO $1 VALUES ( $2 ) ^ QUERY: INSERT INTO $1 VALUES ( $2 ) CONTEXT: SQL statement in PL/PgSQL function "test_function" near line 4 What am I missing? Is it possible to do what I want to do? And if so how should I do it. Note: The function is only to show what the problem is, actual function should look a lot different. Any help will be appreciated Thank you -- Carel Combrink s25291930@tuks.co.za This message and attachments are subject to a disclaimer. Please refer to www.it.up.ac.za/documentation/governance/disclaimer/ for full details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.
Quoting Carel Combrink <s25291930@tuks.co.za>: > Hi, > > I want to create a function in plpgsql that must execute a INSERT query > on the database. The problem comes in I want to create the query inside > the function. Look at the following: > > CREATE OR REPLACE FUNCTION Test_Function(integer) RETURNS VOID > AS $$ > DECLARE > table_name text := 'My_Table_Name'; > BEGIN > INSERT INTO "table_name" VALUES ($1); > END > $$ LANGUAGE plpgsql; > > When I try to create the function I get the following response from Postgres: > ERROR: syntax error at or near "$1" > LINE 1: INSERT INTO $1 VALUES ( $2 ) > ^ > QUERY: INSERT INTO $1 VALUES ( $2 ) > CONTEXT: SQL statement in PL/PgSQL function "test_function" near line 4 > > What am I missing? Is it possible to do what I want to do? And if so > how should I do it. > > Note: The function is only to show what the problem is, actual function > should look a lot different. > > Any help will be appreciated > Thank you > -- > Carel Combrink > s25291930@tuks.co.za > > This message and attachments are subject to a disclaimer. Please refer > to www.it.up.ac.za/documentation/governance/disclaimer/ for full > details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule > onderhewig. Volledige besonderhede is by > www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar. I figured it out using some more reading up in the documentation... http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html I did not get to that page with my previous Google searches sorry. -- Carel Combrink s25291930@tuks.co.za This message and attachments are subject to a disclaimer. Please refer to www.it.up.ac.za/documentation/governance/disclaimer/ for full details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.