Thread: Function problems redux

Hello,

Here is my function. It takes two variables a_artiste(name, forename).

DECLARE
artiste_id individu.individu_id%TYPE;
nom ALIAS FOR $1;
prenom ALIAS FOR $2;
ival INTEGER := nextval('individu_serial');
art TEXT := 'artiste';
curr INTEGER := currval('individu_serial');
BEGIN
SELECT INTO artiste_id * FROM individu WHERE to_ascii(nom) ILIKE
to_ascii(individu.nom) AND to_ascii(prenom) ILIKE
to_ascii(individu.prenom) AND type ILIKE 'collection' OR type2 ILIKE
'artiste';
IF NOT FOUND THEN
INSERT INTO individu (individu_id,nom,prenom,type2) VALUES (ival, nom,
prenom, art)
RETURN curr;
END IF;
RETURN artiste_id;
END;

ERROR:  parser: parse error at or near "return"
DEBUG:  Last error occured while executing PL/pgSQL function a_artiste
DEBUG:  line 11 at SQL statement

This works just fine for known artists but my IF NOT FOUND clause is
seriously broken

HELP =:-D

Cheers
Tony




--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

On 5 Nov 2001, tony wrote:

> Hello,
>
> Here is my function. It takes two variables a_artiste(name, forename).
>
> DECLARE
> artiste_id individu.individu_id%TYPE;
> nom ALIAS FOR $1;
> prenom ALIAS FOR $2;
> ival INTEGER := nextval('individu_serial');
> art TEXT := 'artiste';
> curr INTEGER := currval('individu_serial');
> BEGIN
> SELECT INTO artiste_id * FROM individu WHERE to_ascii(nom) ILIKE
> to_ascii(individu.nom) AND to_ascii(prenom) ILIKE
> to_ascii(individu.prenom) AND type ILIKE 'collection' OR type2 ILIKE
> 'artiste';
> IF NOT FOUND THEN
> INSERT INTO individu (individu_id,nom,prenom,type2) VALUES (ival, nom,
> prenom, art)
> RETURN curr;
> END IF;
> RETURN artiste_id;
> END;
>
> ERROR:  parser: parse error at or near "return"
> DEBUG:  Last error occured while executing PL/pgSQL function a_artiste
> DEBUG:  line 11 at SQL statement
>
> This works just fine for known artists but my IF NOT FOUND clause is
> seriously broken

Don't you need a semicolon at the end of that insert into line in the
if not found?

there is a missing ; in the line

>INSERT INTO individu (individu_id,nom,prenom,type2) VALUES (ival, nom,
>prenom, art)

...

*********** REPLY SEPARATOR  ***********

On 05-11-2001 at 10:23 tony wrote:

>Hello,
>
>Here is my function. It takes two variables a_artiste(name, forename).
>
>DECLARE
>artiste_id individu.individu_id%TYPE;
>nom ALIAS FOR $1;
>prenom ALIAS FOR $2;
>ival INTEGER := nextval('individu_serial');
>art TEXT := 'artiste';
>curr INTEGER := currval('individu_serial');
>BEGIN
>SELECT INTO artiste_id * FROM individu WHERE to_ascii(nom) ILIKE
>to_ascii(individu.nom) AND to_ascii(prenom) ILIKE
>to_ascii(individu.prenom) AND type ILIKE 'collection' OR type2 ILIKE
>'artiste';
>IF NOT FOUND THEN
>INSERT INTO individu (individu_id,nom,prenom,type2) VALUES (ival, nom,
>prenom, art)
>RETURN curr;
>END IF;
>RETURN artiste_id;
>END;
>
>ERROR:  parser: parse error at or near "return"
>DEBUG:  Last error occured while executing PL/pgSQL function a_artiste
>DEBUG:  line 11 at SQL statement
>
>This works just fine for known artists but my IF NOT FOUND clause is
>seriously broken
>
>HELP =:-D
>
>Cheers
>Tony
>
>
>
>
>--
>RedHat Linux on Sony Vaio C1XD/S
>http://www.animaproductions.com/linux2.html
>Macromedia UltraDev with PostgreSQL
>http://www.animaproductions.com/ultra.html
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html

You wrote:
> there is a missing ; in the line
> >INSERT INTO individu (individu_id,nom,prenom,type2) VALUES (ival, nom,
> >prenom, art)

I got that one after hitting send... sorry

Now on to todays question:

how do I get currval and nextval to work in pl/pgsql? They seem to be stuck and won't update during the session.

Cheers

Tony Grant



--
tony@animaproductions.com

JWebMail WebMail/Java v0.7.6 WWW to Mail Gateway