pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedinsert in function writen in pgplsql
9+ messages / 6 participants
[nested] [flat]
* insert in function writen in pgplsql
@ 2010-03-19 13:45 Julius Tuskenis <julius@nsoft.lt>
0 siblings, 3 replies; 9+ messages in thread
From: Julius Tuskenis @ 2010-03-19 13:45 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: insert in function writen in pgplsql
@ 2010-03-19 14:20 Guillaume Lelarge <guillaume@lelarge.info>
parent: Julius Tuskenis <julius@nsoft.lt>
2 siblings, 1 reply; 9+ messages in thread
From: Guillaume Lelarge @ 2010-03-19 14:20 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: insert in function writen in pgplsql
@ 2010-03-19 14:39 Julius Tuskenis <julius@nsoft.lt>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 9+ messages in thread
From: Julius Tuskenis @ 2010-03-19 14:39 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: insert in function writen in pgplsql
@ 2010-03-19 14:48 Guillaume Lelarge <guillaume@lelarge.info>
parent: Julius Tuskenis <julius@nsoft.lt>
0 siblings, 0 replies; 9+ messages in thread
From: Guillaume Lelarge @ 2010-03-19 14:48 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
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-...
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: insert in function writen in pgplsql
@ 2010-03-19 15:00 Anibal David Acosta <anibal.acosta@edge.com.py>
parent: Julius Tuskenis <julius@nsoft.lt>
2 siblings, 0 replies; 9+ messages in thread
From: Anibal David Acosta @ 2010-03-19 15:00 UTC (permalink / raw)
To: 'Julius Tuskenis' <julius@nsoft.lt>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: insert in function writen in pgplsql
@ 2010-03-19 17:27 Szymon Guz <mabewlun@gmail.com>
parent: Julius Tuskenis <julius@nsoft.lt>
2 siblings, 1 reply; 9+ messages in thread
From: Szymon Guz @ 2010-03-19 17:27 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: insert in function writen in pgplsql
@ 2010-03-19 18:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Szymon Guz <mabewlun@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2010-03-19 18:45 UTC (permalink / raw)
To: Szymon Guz <mabewlun@gmail.com>; +Cc: Julius Tuskenis <julius@nsoft.lt>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: insert in function writen in pgplsql
@ 2010-03-19 19:16 Szymon Guz <mabewlun@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Szymon Guz @ 2010-03-19 19:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Julius Tuskenis <julius@nsoft.lt>; pgsql-admin
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
regards,
szymon guz
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: insert in function writen in pgplsql
@ 2010-03-22 22:47 Bruce Momjian <bruce@momjian.us>
parent: Szymon Guz <mabewlun@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2010-03-22 22:47 UTC (permalink / raw)
To: Szymon Guz <mabewlun@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Julius Tuskenis <julius@nsoft.lt>; 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-03-22 22:47 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-19 13:45 insert in function writen in pgplsql Julius Tuskenis <julius@nsoft.lt>
2010-03-19 14:20 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-03-19 14:39 ` Julius Tuskenis <julius@nsoft.lt>
2010-03-19 14:48 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-03-19 15:00 ` Anibal David Acosta <anibal.acosta@edge.com.py>
2010-03-19 17:27 ` Szymon Guz <mabewlun@gmail.com>
2010-03-19 18:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-19 19:16 ` Szymon Guz <mabewlun@gmail.com>
2010-03-22 22:47 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox