Postgres Pro
Downloads
Home   >   mailing lists

Thread: passing values to a function

passing values to a function

From
Cedar Cox
Date:
Why does this not work?  Am I doing something wrong?

CREATE FUNCTION uutest(text) RETURNS bool AS '
declare varop alias for $1;
begin raise notice '' varop is '',varop; return false;
end;
' LANGUAGE 'plpgsql';

Givingselect uutest('INSERT');
returns

NOTICE:   varop is uutest 
--------f
(1 row)

Has my PG server entered some weird state or something?

One other question.. Is there an easy way to pass an entire record to a
function (eg, the 'new' record)?  Presently I am passing all 12 fields as
separate parameters.

-Cedar

Re: passing values to a function

From
Jan Wieck
Date:
Cedar Cox wrote:
>
> Why does this not work?  Am I doing something wrong?
>
> CREATE FUNCTION uutest(text) RETURNS bool AS '
> declare
>   varop alias for $1;
> begin
>   raise notice '' varop is '',varop;
   raise notice '' varop is %'', varop;

>   return false;
> end;
> ' LANGUAGE 'plpgsql';
> [...]
>
> One other question.. Is there an easy way to pass an entire record to a
> function (eg, the 'new' record)?  Presently I am passing all 12 fields as
> separate parameters.
   Yes and no.
   Yes,  PL/pgSQL functions can receive tuples as arguments. The   only oddity is that they *must* get  aliased  for
access of   attributes. Your function would look like:
 
       CREATE FUNCTION myfunc (mytable) RETURNS text AS '       DECLARE           row ALIAS FOR %1;       BEGIN
 return row.key || '' equals '' row.value;       END;'       LANGUAGE 'plpgsql';
 
   But unfortunately (and there's no really good reason why) no,   a PL/pgSQL function  having  some  row  somehow
cannot call   another  function  handing that row over as a tuple argument.   Something to work on for 7.2.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: passing values to a function

From
Cedar Cox
Date:
> > Why does this not work?  Am I doing something wrong?
..
> >   raise notice '' varop is '',varop;
> 
>     raise notice '' varop is %'', varop;

Arrrg!  How did I miss that one.. I've been writing notice lines for
months now!

Ok, well I seem to have found the "real" problem this time ;)

CREATE FUNCTION update_sentitems(text,int4) RETURNS bool AS '
declare varop alias for $1;
begin raise notice '' varop = %'',varop; return false;
end;
' LANGUAGE 'plpgsql';

Giving select update_sentitems('INSERT',1);
returns NOTICE:   varop = INSERT
as expected, however if you give select update_sentitems('INSERT',null);
this is returned: NOTICE:   varop = <NULL>

BTW, it doesn't matter if both parameters are text or int4.  Also, it
doesn't matter which one is given as null, they both turn up null.  
Bottom line seems to be that if you call the function with any parameter
null, they are all null.  Sooo.. what's up here?  Is this a bug that's
been fixed in 7.0.3? (I'm still using 7.0.2).

Thanks
-Cedar

Re: passing values to a function

From
Jan Wieck
Date:
Cedar Cox wrote:
> Giving
>   select update_sentitems('INSERT',1);
> returns
>   NOTICE:   varop = INSERT
> as expected, however if you give
>   select update_sentitems('INSERT',null);
> this is returned:
>   NOTICE:   varop = <NULL>
>
> BTW, it doesn't matter if both parameters are text or int4.  Also, it
> doesn't matter which one is given as null, they both turn up null.
> Bottom line seems to be that if you call the function with any parameter
> null, they are all null.  Sooo.. what's up here?  Is this a bug that's
> been fixed in 7.0.3? (I'm still using 7.0.2).
   I  wouldn't call it a bug, it was a lack of capabilities with   the pre7.1 function manager. It had only one flag
forNULL to   pass  to  the  function,  so  it said "there is a NULL, but I   don't tell you which arg it is".
 
   7.1 is the first that does it correct.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

large object descriptor

From
Manika dey
Date:
Hi,
 Whenever i try to acces my data using servlets i get error 
 Fastpath: ERROR:  lo_tell: invalid large object descriptor (-1)
 I would be thanful if any body can help towards it.
 JDK , jsdk2.0 , postgresql on solaris 
thanks
manika

-------------------------------------------------------------------

From:-                          | Ms. Manika Dey.                |Ph.No:--Engineer-SC (Comp. Tech.)      | IPR -- 02712
-69276 I.P.R                          |          EXT 336,315BHAT, GANDHINAGAR              | Residence -- 079 -7421117
Gujrat-- 382 428              | FAX --- 69017 ------------------------------------------------------------------

Re: large object descriptor

From
Peter T Mount
Date:
Quoting Manika dey <bithi@ipr.res.in>:

> 
> Hi,
> 
>   Whenever i try to acces my data using servlets
>   i get error 
> 
>   Fastpath: ERROR:  lo_tell: invalid large object descriptor (-1)

turn off AutoCommit using: setAutoCommit(false);

To use large objects you must be within a transaction.

Peter

-- 
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/