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

Re: insert in function writen in pgplsql

Le 19/03/2010 14:45, Julius Tuskenis a écrit :
> Hello
>=20
> 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?
>=20

Yes. It would also be easier to debug.


--=20
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: insert in function writen in pgplsql

2010.03.19 16:20, Guillaume Lelarge rašė:
> Yes. It would also be easier to debug.
> =20
I guess so, but is there no way to tell the postgres that I'm listing=20
the field names and there is no place for a parameter in that list... I=20
tried using INSERT INTO table(table.column .... syntax, but postgres=20
does not accept that.
When you write a new function it is not hard to pick the parameter names=20
you want, but when editing it you have to play with what you have and=20
sometimes it's a pain....

One more question Is there a way to use INSERT INTO table($1....=20
syntax?? I can't think of such case. Can you provide an example?

--=20
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: insert in function writen in pgplsql

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.
>> =20
> 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 name=
s
> you want, but when editing it you have to play with what you have and
> sometimes it's a pain....
>=20

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?
>=20

You'll find examples and explanations in the fine manual:


http://www.postgresql.org/docs/8.4/interactive/plpgsql-state ments.html#PL=
PGSQL-STATEMENTS-EXECUTING-DYN


--=20
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: insert in function writen in pgplsql

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.o=
rg] 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=20
varchar(20)). In its body I want to have statement INSERT INTO=20
my_users(user, password) VALUES (user, password); The problem is I cant=20
- then I try to create such function I get "ERROR: syntax error at or=20
near "$1"
LINE 1: INSERT INTO my_users( $1 .... " This tells me the parameter is=20
used instead of column name. That is of course not what I wanted. What=20
would you recommend to fix this? Is there any way to tell the postgres=20
that user is column name? I tried "user" with same error. Must I change=20
the names of parameters?

using PG 8.3

--=20
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: insert in function writen in pgplsql

--0016e65c7c20a41b7f04822aa832
Content-Type: text/plain; charset=UTF-8

2010/3/19 Julius Tuskenis

> 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

--0016e65c7c20a41b7f04822aa832
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Re: insert in function writen in pgplsql

Szymon Guz 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: insert in function writen in pgplsql

--0016e6d9773568202004822c2e4f
Content-Type: text/plain; charset=UTF-8

2010/3/19 Tom Lane

> Szymon Guz 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

regards,
szymon guz

--0016e6d9773568202004822c2e4f
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Re: insert in function writen in pgplsql

Szymon Guz wrote:
> 2010/3/19 Tom Lane
>
> > Szymon Guz 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 http://momjian.us
EnterpriseDB http://enterprisedb.com

PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin