Thread: error with functions

error with functions

From
shyamperi@davlin.co.in
Date:
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

Re: error with functions

From
Stephan Szabo
Date:
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?

Re: error with functions

From
shyamperi@davlin.co.in
Date:
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

Re: error with functions

From
Stephan Szabo
Date:
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?


Re: error with functions

From
shyamperi@davlin.co.in
Date:
>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

Re: error with functions

From
Oliver Elphick
Date:
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


Re: error with functions

From
Stephan Szabo
Date:
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"