Thread: error with functions
10:12a
Dear all,
This is about the same problem which I have been facing for the past one week and which I am unable to solve.
I have create a function which return +1 of the argument passed.
Function Definition:
CREATE FUNCTION add_one (integer) RETURNS INTEGER AS '
BEGIN
RETURN $1 + 1;
END;
' LANGUAGE 'plpgsql';
The output it was giving was always 1, despite any argument ( not string's ofcourse)
When I viewed the function definition in the pg_proc table...
psql "select proname,prosrc from pg_proc where proname='add_one'"
proname | prosrc
---------+-------------------------------------------------------
add_one | BEGIN RETURN +1 END;
(1 row)
______________
Can any body tell me why is the $1 missing..
I am using running postgres 7.3.4 in linux envt
Have a grate day
-----
Warm Regards
Shÿam Peri
II Floor, Punja Building,
M.G.Road,
Ballalbagh,
Mangalore-575003
Ph : 91-824-2451001/5
Fax : 91-824-2451050
----------------Original Message------------------
Subject : Re: [ADMIN] Postgresql UPDATE LOCKS unrelated rows.
Hi Stephan,
Here is the table structure. FYI, we tried the experiment with mysql
and the concurrency is working fine.
create table TABLE1( TABLE_ID IDENTITY NOT NULL ,
VA_ID SMALLINT NOT NULL,
V_STATES_ID SMALLINT NOT
NULL,
V_VOL VARCHAR(6),
V_OBJ_ID INT,
V_CELL SMALLINT,
V_POOL VARCHAR(255),
FOREIGN KEY(VA_ID) REFERENCES
TABLE2(VA_ID) ON DELETE CASCADE,
FOREIGN KEY(V_STATES_ID)
REFERENCES V_STATES(V_STATES_ID),
PRIMARY KEY(TABLE_ID));
We were trying to update V_VOL using following statement:
UPDATE TABLE1 SET V_VOL = 'ABCD' WHERE TABLE_ID = 100000
Regards,
Ravi
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Thursday, September 18, 2003 8:32 PM
To: Ravi T Ramachandra
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql UPDATE LOCKS unrelated rows.
On Thu, 18 Sep 2003, Ravi T Ramachandra wrote:
>
> Hi,
>
> We are using postgresql 7.3.2 on lunux platform. We performed
locking
> test as follows via JDBC:
>
> Started 2 concurrent transaction with autocommit false and isolation
> READ_COMMITTED :
>
> Transaction 1:
> ===========
> Update row 1
> Sleep for 30 seconds
> commit:
>
> Transaction 2:
> ===========
> Updated row 10000
> commit
>
> The second update had to wait for the first update operation to
> finish. We also made sure that the 2 unrelated rows are not on the
> same page.
>
> Is this expected behavior ?
> Does update lock the whole table ?
Generally not, but you really haven't given alot of information. What
is the table layout for the table in question? Are there foreign keys
involved?
**************************Disclaimer************************************
Information contained in this E-MAIL being proprietary to Wipro
Limited is
'privileged' and 'confidential' and intended for use only by the
individual
or entity to which it is addressed. You are notified that any use,
copying
or dissemination of the information contained in the E-MAIL in any
manner
whatsoever is strictly prohibited.
***************************************************************************
---------------------------(end of
broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match
DISCLAIMER: This message contains privileged and confidential information and is
intended only for the individual named.If you are not the intended
recipient you should not disseminate,distribute,store,print, copy or
deliver this message.Please notify the sender immediately by e-mail if
you have received this e-mail by mistake and delete this e-mail from
your system.
Attachment
On Mon, 22 Sep 2003 shyamperi@davlin.co.in wrote: > Dear all, > This is about the same problem which I have been facing for the past one week and which I am unable to solve. > I have create a function which return +1 of the argument passed. > Function Definition: > CREATE FUNCTION add_one (integer) RETURNS INTEGER AS ' > BEGIN > RETURN $1 + 1; > END; > ' LANGUAGE 'plpgsql'; > > The output it was giving was always 1, despite any argument ( not string's ofcourse) > When I viewed the function definition in the pg_proc table... > > psql "select proname,prosrc from pg_proc where proname='add_one'" > proname | prosrc > ---------+------------------------------------------------------- > add_one | BEGIN RETURN +1 END; > (1 row) > ______________ > Can any body tell me why is the $1 missing.. > I am using running postgres 7.3.4 in linux envt > Have a grate day How are you creating the function?
Command: psql test -c "> CREATE FUNCTION add_one (integer) RETURNS INTEGER AS ' BEGIN REURN $1 + 1; END;' LANGUAGE 'plpgsql';" Result: CREATE FUNCTION And the function executed perfectly fine. ----- Warm Regards Shÿam Peri II Floor, Punja Building, M.G.Road, Ballalbagh, Mangalore-575003 Ph : 91-824-2451001/5 Fax : 91-824-2451050 ----------------Original Message------------------ On Mon, 22 Sep 2003 shyamperi@davlin.co.in wrote: > Dear all, > This is about the same problem which I have been facing for the past one week and which I am unable to solve. > I have create a function which return +1 of the argument passed. > Function Definition: > CREATE FUNCTION add_one (integer) RETURNS INTEGER AS ' > BEGIN > RETURN $1 + 1; > END; > ' LANGUAGE 'plpgsql'; > > The output it was giving was always 1, despite any argument ( not string's ofcourse) > When I viewed the function definition in the pg_proc table... > > psql "select proname,prosrc from pg_proc where proname='add_one'" > proname | prosrc > ---------+------------------------------------------------------- > add_one | BEGIN RETURN +1 END; > (1 row) > ______________ > Can any body tell me why is the $1 missing.. > I am using running postgres 7.3.4 in linux envt > Have a grate day How are you creating the function? DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.
Attachment
On Mon, 22 Sep 2003 shyamperi@davlin.co.in wrote: > Command: psql test -c "> CREATE FUNCTION add_one (integer) RETURNS > INTEGER AS ' BEGIN REURN $1 + 1; END; ' LANGUAGE > 'plpgsql';" You do realize that the shell is going to interpret that string in double quotes right?
>You do realize that the shell is going to interpret that string in >double quotes right? I guess so, for the very same I have tried with Command: psql test -c "> CREATE FUNCTION add_one (integer) RETURNS INTEGER AS ' BEGIN RETURN ''$1'' + 1; END; ' LANGUAGE 'plpgsql';" But still it is not taking it as $1, instead empty string. Ideeally it shud consider $1 as string... but why isn't it ----- Warm Regards Shÿam Peri II Floor, Punja Building, M.G.Road, Ballalbagh, Mangalore-575003 Ph : 91-824-2451001/5 Fax : 91-824-2451050 ----------------Original Message------------------ On Mon, 22 Sep 2003 shyamperi@davlin.co.in wrote: > Command: psql test -c "> CREATE FUNCTION add_one (integer) RETURNS > INTEGER AS ' BEGIN REURN $1 + 1; END; ' LANGUAGE > 'plpgsql';" You do realize that the shell is going to interpret that string in double quotes right? ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.
Attachment
On Mon, 2003-09-22 at 06:42, shyamperi@davlin.co.in wrote:
> >You do realize that the shell is going to interpret that string in
> >double quotes right?
> I guess so, for the very same I have tried with
> Command: psql test -c "> CREATE FUNCTION add_one (integer) RETURNS
> INTEGER AS ' BEGIN RETURN ''$1'' + 1; END; ' LANGUAGE
> 'plpgsql';"
> But still it is not taking it as $1, instead empty string.
> Ideeally it shud consider $1 as string... but why isn't it
It's still being interpreted by the shell. The shell takes no notice at
all of the nested quotes; everything inside the double quotes is treated
as literal text, after replacing variables. Look here:
olly@linda$ echo psql test -c "> CREATE FUNCTION add_one
(integer) RETURNS INTEGER AS ' BEGIN RETURN ''$1'' +
1; END; ' LANGUAGE 'plpgsql';"
psql test -c > CREATE FUNCTION add_one (integer) RETURNS INTEGER
AS ' BEGIN RETURN '''' + 1; END; ' LANGUAGE
'plpgsql';
Instead of trying to quote $1, you need to escape the dollar sign:
olly@linda$ echo psql test -c "> CREATE FUNCTION add_one
(integer) RETURNS INTEGER AS ' BEGIN RETURN \$1 +
1; END; ' LANGUAGE 'plpgsql';"
psql test -c > CREATE FUNCTION add_one (integer) RETURNS INTEGER
AS ' BEGIN RETURN $1 + 1; END; ' LANGUAGE
'plpgsql';
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Therefore when thou doest thine alms, do not sound a
trumpet before thee, as the hypocrites do in the
synagogues and in the streets, that they may have
glory of men. Verily I say unto you, They have their
reward. But when thou doest alms, let not thy left
hand know what thy right hand doeth; That thine alms
may be in secret; and thy Father which seeth in secret
himself shall reward thee openly." Matthew 6:2-4
On Mon, 22 Sep 2003 shyamperi@davlin.co.in wrote: > >You do realize that the shell is going to interpret that string in > >double quotes right? > I guess so, for the very same I have tried with > Command: psql test -c "> CREATE FUNCTION add_one (integer) RETURNS > INTEGER AS ' BEGIN RETURN ''$1'' + 1; END; ' LANGUAGE > 'plpgsql';" > But still it is not taking it as $1, instead empty string. > Ideeally it shud consider $1 as string... but why isn't it The shell interprets the double quoted string and effectively replaces $1 with the shell's idea of what to put in place of it. This really has nothing to do with postgres at all. Use a backslash before the $1, or put it in a file and execute it from there. Try these for example: echo "$1" echo '$1' echo "\$1"