Thread: Functions

All,

I'm trying to figure out how functions work in PGSQL.  I've got a sample here but I keep getting an error and I'm not sure why.

CREATE FUNCTION test2(VARCHAR) RETURNS SETOF TEXT AS
'
DECLARE
 r record;
BEGIN
FOR r IN
 select router_name from router where router_name ~ $1
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

Then when I run this command:
select * from test2('houston');

I get an error:
WARNING:  Error occurred while executing PL/pgSQL function test2
WARNING:  line 7 at return next
ERROR:  Attribute "r" not found

I'm not sure why I get this error, i thought I declared it in the declare section.

--
Thanks,
Nick

On May 26, 2005, at 4:07 PM, Nick Jones wrote:

>  I'm trying to figure out how functions work in PGSQL.  I've got a
> sample here but I keep getting an error and I'm not sure why.
>
>  CREATE FUNCTION test2(VARCHAR) RETURNS SETOF TEXT AS
>  '
>  DECLARE
>   r record;
>  BEGIN
>  FOR r IN
>   select router_name from router where router_name ~ $1
>  LOOP
>  RETURN NEXT r;
>  END LOOP;
>  RETURN;
>  END;
>  ' LANGUAGE 'plpgsql';
>
>  Then when I run this command:
>  select * from test2('houston');
>
>  I get an error:
>  WARNING:  Error occurred while executing PL/pgSQL function test2
>  WARNING:  line 7 at return next
>  ERROR:  Attribute "r" not found
>
>  I'm not sure why I get this error, i thought I declared it in the
> declare section.
>
>

r has the structure of table in your select, namely 'router'. Try

RETURN NEXT r.router_name;

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Nick Jones <neckjonez@gmail.com> writes:
> CREATE FUNCTION test2(VARCHAR) RETURNS SETOF TEXT AS
> '
> DECLARE
> r record;
> BEGIN
> FOR r IN
> select router_name from router where router_name ~ $1
> LOOP
> RETURN NEXT r;
> END LOOP;
> RETURN;
> END;
> ' LANGUAGE 'plpgsql';

Close, but you want "RETURN NEXT r.router_name".  r is a record
potentially containing several fields, you have to say which you
want.

            regards, tom lane

Aww, I was really close :) ... thanks, that works.  I believe I understand it now :)  Do you normally write functions in plpgsql or a different language (sql, perl, c)?  I ask so I should know where to focus my learning.

Thanks,
Nick




--
Thanks,
Nick

On May 31, 2005, at 2:52 PM, Nick Jones wrote:

> Aww, I was really close :) ... thanks, that works.  I believe I
> understand it now :)  Do you normally write functions in plpgsql or a
> different language (sql, perl, c)?  I ask so I should know where to
> focus my learning.


I like plpsql because the semantics are designed for working with
databases and PostgreSQL in particular. I would only use a different
language for PostgreSQL if plpgsql can't get the job done or cannot do
it efficiently.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

John DeSoi wrote:
>
> On May 31, 2005, at 2:52 PM, Nick Jones wrote:
>
>> Aww, I was really close :) ... thanks, that works.  I believe I
>> understand it now :)  Do you normally write functions in plpgsql or a
>> different language (sql, perl, c)?  I ask so I should know where to
>> focus my learning.
>
> I like plpsql because the semantics are designed for working with
> databases and PostgreSQL in particular. I would only use a different
> language for PostgreSQL if plpgsql can't get the job done or cannot do
> it efficiently.
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL

While I agree with John and write 98% of my functions in plpsql I found
it necessary to write a couple of functions in Perl because I had to
parse a text field that was sufficiently complex as to almost completely
rule out plpsql.

You will need to focus your learning on whatever makes sense for the
majority of your application.  The good news is that plpsql comes almost
for free so you still get one more. ;-)

--
Kind Regards,
Keith

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Keith Worthington wrote:
> John DeSoi wrote:
>
>>
>> On May 31, 2005, at 2:52 PM, Nick Jones wrote:
>>
>>> Aww, I was really close :) ... thanks, that works.  I believe I
>>> understand it now :)  Do you normally write functions in plpgsql or a
>>> different language (sql, perl, c)?  I ask so I should know where to
>>> focus my learning.
>>
>>
>> I like plpsql because the semantics are designed for working with
>> databases and PostgreSQL in particular. I would only use a different
>> language for PostgreSQL if plpgsql can't get the job done or cannot do
>> it efficiently.
>>
>> John DeSoi, Ph.D.
>> http://pgedit.com/
>> Power Tools for PostgreSQL
>
>
> While I agree with John and write 98% of my functions in plpsql I found
> it necessary to write a couple of functions in Perl because I had to
> parse a text field that was sufficiently complex as to almost completely
> rule out plpsql.
>
> You will need to focus your learning on whatever makes sense for the
> majority of your application.  The good news is that plpsql comes almost
> for free so you still get one more. ;-)

If I can reasonably write it as an SQL function, then I do it that way (
because it's easiest to read/maintain and might be a little faster).

Otherwise, if I can reasonably write it in plpgsql, then I do it that
way (for the reasons John laid out below and because it's still easy to
read/maintain and harder to shoot myself in the foot with).

Finally, I resort to plpython or plperl when there's "No Other Way".
I've never been in the position where I actually had to write c.

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFCnc9Wgfzn5SevSpoRAlmmAKCQ8uH1u2z8E9Mzxof57J/l2NhBvQCgpISX
0WS6ftMV5zq4XYd941Lbuwk=
=U2fM
-----END PGP SIGNATURE-----

Thanks for all the info guys!  I appreciate it.




--
Thanks,
Nick

Oops! desoi@pgedit.com (John DeSoi) was seen spray-painting on a wall:
> On May 31, 2005, at 2:52 PM, Nick Jones wrote:
>
>> Aww, I was really close :) ... thanks, that works.  I believe I
>> understand it now :)  Do you normally write functions in plpgsql or
>> a different language (sql, perl, c)?  I ask so I should know where
>> to focus my learning.
>
> I like plpsql because the semantics are designed for working with
> databases and PostgreSQL in particular. I would only use a different
> language for PostgreSQL if plpgsql can't get the job done or cannot do
> it efficiently.

Perl or Python would seem preferable for scenarios where you're doing
complex manipulations on strings, as that's something those languages
can really bear down on.

For code that's really time critical, C may be necessary, but at the
cost that much more debugging need be done lest you risk Bad Crashes,
since C can "do anything" including corrupting memory nearly
spontaneously :-).

I haven't quite figured out the "use case" for pl/Java, except for the
possibility that it allows making use of third party Java classes.
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/lsf.html
MICROS~1 is not the answer.
MICROS~1 is the question.
NO (or Linux) is the answer.