Обсуждение: 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.