Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 0D92F633D36
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 19 Mar 2010 14:27:29 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 99341-01 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 19 Mar 2010 17:27:16 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com
 [74.125.82.46])
	by mail.postgresql.org (Postfix) with ESMTP id DC4B4632759
	for <pgsql-admin@postgresql.org>; Fri, 19 Mar 2010 14:27:17 -0300 (ADT)
Received: by wwe15 with SMTP id 15so1970203wwe.19
	for <pgsql-admin@postgresql.org>; Fri, 19 Mar 2010 10:27:16 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=QmzGPPCUrdjm/Y2XwkWM+9myKzny0G1LaoZe/9B70HA=;
	b=ER3crqMPQguWxIkV+An8I/FgeHAvSSbKsTAfUUC0ME4ffoZceFWwmlPOTNR2gnbIjJ
	3b+xvC3arfmX3Ns2J+3z+DuxGdFckX++rhvE1fHgzp9GgSdJUqhDEfePN+4H3eCKFpZ6
	2eHDTYKZ9Kmyfs5U5jqoptl6OrJf2zpqX/wUo=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=blWp91wUDWK2YEmCxtZPjiKxRmlSgXHvvHzcIlH7oxmPssjVaSKkFSfC9nXpqjUKKQ
	HXYXFnArAudOb5bJR/qYJDGY1alMXNGm6ssfK8cVZ58D4G96VK5GiLdV8wirDSviXV4O
	w5yN+jOxH9venCWDeS5OPST5IgoN3FDw4UPIY=
MIME-Version: 1.0
Received: by 10.216.177.15 with SMTP id c15mr1286626wem.103.1269019636405;
	Fri, 19 Mar 2010 10:27:16 -0700 (PDT)
In-Reply-To: <4BA37FF5.8060001@nsoft.lt>
References: <4BA37FF5.8060001@nsoft.lt>
Date: Fri, 19 Mar 2010 18:27:16 +0100
Message-ID: <e4edc9361003191027j785b8ba1oc5d5e7e0f59fe076@mail.gmail.com>
Subject: Re: insert in function writen in pgplsql
From: Szymon Guz <mabewlun@gmail.com>
To: Julius Tuskenis <julius@nsoft.lt>
Cc: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary=0016e65c7c20a41b7f04822aa832
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.165 tagged_above=-10 required=5 tests=AWL=0.433,
	BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/143
X-Sequence-Number: 33942

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

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

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

<div class=3D"gmail_quote">2010/3/19 Julius Tuskenis <span dir=3D"ltr">&lt;=
<a href=3D"mailto:julius@nsoft.lt" target=3D"_blank">julius@nsoft.lt</a>&gt=
;</span><br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;bo=
rder-left:1px #ccc solid;padding-left:1ex">

Hello<br>
<br>
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 &quot;ERROR: =C2=A0syntax error at or near &quot;$1&quot;<b=
r>


LINE 1: INSERT INTO my_users( =C2=A0$1 .... &quot; This tells me the parame=
ter is used instead of column name. That is of course not what I wanted. Wh=
at would you recommend to fix this? Is there any way to tell the postgres t=
hat user is column name? I tried &quot;user&quot; with same error. Must I c=
hange the names of parameters?<br>


<br>
using PG 8.3<br>
<br></blockquote><div><br></div><div>You won&#39;t have to change the param=
eters in the function definition, instead you can use the RENAME clause:</d=
iv><div><br></div><div>CREATE OR REPLACE FUNCTION add_user(user varchar(20)=
, password varchar(20) ) RETURNS VOID AS $$</div>

<div>DECLARE</div><div>=C2=A0=C2=A0RENAME user TO x_user;=C2=A0</div><div>=
=C2=A0=C2=A0RENAME pa TO x_password;=C2=A0</div><div>BEGIN</div><div>=C2=A0=
=C2=A0INSERT INTO my_users(user, password) VALUES (x_user, x_password);</di=
v><div>END; LANGUAGE plgpsql;=C2=A0</div>
<div><br></div><div>regards</div><div>Szymon</div></div>

--0016e65c7c20a41b7f04822aa832--