how postgresql passes the parameter to the function. syntax error near or at "$1".

Hello

parameter - $n cannot be used on table name or column name position.
Some statements - DROP, VACUUM, ANALYZE doesn't support parameters
ever.

You have to use dynamic SQL in these cases.

CREATE OR REPLACE FUNCTION foo(tablename text, value text)
RETURNS void AS $$
BEGIN
EXECUTE 'insert into ' || quote_ident(tablename) || ' VALUES($1)' USING value;
END;
$$ LANGUAGE plpgsql

Regards

Pavel Stehule

2012/3/28 leaf_yxj <leaf_yxj@163.com>:

Pavel,

Thanks a lot.

Regards.

Grace

At 2012-03-29 00:27:12,"Pavel Stehule [via PostgreSQL]" <ml-node+s1045698n5601077h29@n5.nabble.com> wrote:
Hello

parameter - $n cannot be used on table name or column name position.
Some statements - DROP, VACUUM, ANALYZE doesn't support parameters
ever.

You have to use dynamic SQL in these cases.

CREATE OR REPLACE FUNCTION foo(tablename text, value text)
RETURNS void AS $$
BEGIN
EXECUTE 'insert into ' || quote_ident(tablename) || ' VALUES($1)' USING value;
END;
$$ LANGUAGE plpgsql

Regards

Pavel Stehule

2012/3/28 leaf_yxj <[hidden email]>:

I want to create a function which use to truncate the table specified by the
caller. I very confused how postgresql pass this parameter into the function
:

as a superuser to execute :
1)
create or replace function d() returns void as $$
analyze;
$$ language sql;

----- this works when i issue select d()

2) this doesn't work

create or replace function v(text) returns void as $$
analyze $1;
$$ language sql;

why???? and how to correct it?

I tried another function for insert

1) this works.

create or replace function insert_f(integer) returns void as $$
insert into t1 values($1);
$$ language sql;

----this works when i issue select insert_f(20);

2) this doesn't work.

create or replace function insert_f(text,integer) returns void as $$
insert into $1 values($2);
$$ language sql;

---- it failed to create the function and give me error : syntax error at or
near "$1".

Please help.

Thanks.

Regards.

Grace

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601053.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601077.html
To unsubscribe from how postgresql passes the parameter to the function. syntax error near or at "$1"., click here.
NAML
T

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-postgresql-passes-the-parameter-to-the-function-syntax-error-near-or-at-1-tp5601053p5601106.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.