Обсуждение: insert in function writen in pgplsql
Hello lets say I have a function add_user(user varchar(20), password varchar(20)). In its body I want to have statement INSERT INTO my_users(user, password) VALUES (user, password); The problem is I cant - then I try to create such function I get "ERROR: syntax error at or near "$1" LINE 1: INSERT INTO my_users( $1 .... " This tells me the parameter is used instead of column name. That is of course not what I wanted. What would you recommend to fix this? Is there any way to tell the postgres that user is column name? I tried "user" with same error. Must I change the names of parameters? using PG 8.3 -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050
Le 19/03/2010 14:45, Julius Tuskenis a écrit : > Hello > > lets say I have a function add_user(user varchar(20), password > varchar(20)). In its body I want to have statement INSERT INTO > my_users(user, password) VALUES (user, password); The problem is I cant > - then I try to create such function I get "ERROR: syntax error at or > near "$1" > LINE 1: INSERT INTO my_users( $1 .... " This tells me the parameter is > used instead of column name. That is of course not what I wanted. What > would you recommend to fix this? Is there any way to tell the postgres > that user is column name? I tried "user" with same error. Must I change > the names of parameters? > Yes. It would also be easier to debug. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
2010.03.19 16:20, Guillaume Lelarge rašė: > Yes. It would also be easier to debug. > I guess so, but is there no way to tell the postgres that I'm listing the field names and there is no place for a parameter in that list... I tried using INSERT INTO table(table.column .... syntax, but postgres does not accept that. When you write a new function it is not hard to pick the parameter names you want, but when editing it you have to play with what you have and sometimes it's a pain.... One more question Is there a way to use INSERT INTO table($1.... syntax?? I can't think of such case. Can you provide an example? -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050
Le 19/03/2010 15:39, Julius Tuskenis a écrit : > 2010.03.19 16:20, Guillaume Lelarge rašė: >> Yes. It would also be easier to debug. >> > I guess so, but is there no way to tell the postgres that I'm listing > the field names and there is no place for a parameter in that list... I > tried using INSERT INTO table(table.column .... syntax, but postgres > does not accept that. No, there's no way. > When you write a new function it is not hard to pick the parameter names > you want, but when editing it you have to play with what you have and > sometimes it's a pain.... > I always add prefix to work around this (p_user for example). > One more question Is there a way to use INSERT INTO table($1.... > syntax?? I can't think of such case. Can you provide an example? > You'll find examples and explanations in the fine manual: http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
2010/3/19 Julius Tuskenis <julius@nsoft.lt>
Hello
lets say I have a function add_user(user varchar(20), password varchar(20)). In its body I want to have statement INSERT INTO my_users(user, password) VALUES (user, password); The problem is I cant - then I try to create such function I get "ERROR: syntax error at or near "$1"
LINE 1: INSERT INTO my_users( $1 .... " This tells me the parameter is used instead of column name. That is of course not what I wanted. What would you recommend to fix this? Is there any way to tell the postgres that user is column name? I tried "user" with same error. Must I change the names of parameters?
using PG 8.3
You won't have to change the parameters in the function definition, instead you can use the RENAME clause:
CREATE OR REPLACE FUNCTION add_user(user varchar(20), password varchar(20) ) RETURNS VOID AS $$
DECLARE
RENAME user TO x_user;
RENAME pa TO x_password;
BEGIN
INSERT INTO my_users(user, password) VALUES (x_user, x_password);
END; LANGUAGE plgpsql;
regards
Szymon
Szymon Guz <mabewlun@gmail.com> writes:
> You won't have to change the parameters in the function definition, instead
> you can use the RENAME clause:
> CREATE OR REPLACE FUNCTION add_user(user varchar(20), password varchar(20) )
> RETURNS VOID AS $$
> DECLARE
> RENAME user TO x_user;
> RENAME pa TO x_password;
> BEGIN
I wouldn't recommend relying on that, because RENAME has been removed in
9.0. I'm not convinced that it would have worked as desired in previous
versions either.
regards, tom lane
2010/3/19 Tom Lane <tgl@sss.pgh.pa.us>
Szymon Guz <mabewlun@gmail.com> writes:I wouldn't recommend relying on that, because RENAME has been removed in
> You won't have to change the parameters in the function definition, instead
> you can use the RENAME clause:
> CREATE OR REPLACE FUNCTION add_user(user varchar(20), password varchar(20) )
> RETURNS VOID AS $$
> DECLARE
> RENAME user TO x_user;
> RENAME pa TO x_password;
> BEGIN
9.0. I'm not convinced that it would have worked as desired in previous
versions either.
regards, tom lane
Is alias also removed?
I've checked that and it works in 8.4
regards,
szymon guz
Try function add_user(_user varchar(20), _password varchar(20)) INSERT INTO my_users(user, password) VALUES (_user, _password); -----Mensaje original----- De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de Julius Tuskenis Enviado el: viernes, 19 de marzo de 2010 09:45 a.m. Para: pgsql-admin@postgresql.org Asunto: [ADMIN] insert in function writen in pgplsql Hello lets say I have a function add_user(user varchar(20), password varchar(20)). In its body I want to have statement INSERT INTO my_users(user, password) VALUES (user, password); The problem is I cant - then I try to create such function I get "ERROR: syntax error at or near "$1" LINE 1: INSERT INTO my_users( $1 .... " This tells me the parameter is used instead of column name. That is of course not what I wanted. What would you recommend to fix this? Is there any way to tell the postgres that user is column name? I tried "user" with same error. Must I change the names of parameters? using PG 8.3 -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Szymon Guz wrote:
> 2010/3/19 Tom Lane <tgl@sss.pgh.pa.us>
>
> > Szymon Guz <mabewlun@gmail.com> writes:
> > > You won't have to change the parameters in the function definition,
> > instead
> > > you can use the RENAME clause:
> >
> > > CREATE OR REPLACE FUNCTION add_user(user varchar(20), password
> > varchar(20) )
> > > RETURNS VOID AS $$
> > > DECLARE
> > > RENAME user TO x_user;
> > > RENAME pa TO x_password;
> > > BEGIN
> >
> > I wouldn't recommend relying on that, because RENAME has been removed in
> > 9.0. I'm not convinced that it would have worked as desired in previous
> > versions either.
> >
> > regards, tom lane
> >
>
> Is alias also removed?
> I've checked that and it works in 8.4
ALIAS is the recommended replacement for RENAME:
http://developer.postgresql.org/pgdocs/postgres/release-9-0.html
o Remove PL/pgSQL's RENAME declaration option (Tom)
Instead, use ALIAS, which can now alias any variable, not just dollar
sign variables, e.g. $1.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do