Function problems redux
Function problems redux
От:
tony <tony@animaproductions.com>
Дата:
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
Re: Function problems redux
От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:
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?
Re: Function problems redux
От:
"Johnny J\xF8rgensen" <pgsql@halfahead.dk>
Дата:
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
Re: Function problems redux
От:
tony <tony@animaproductions.com>
Дата:
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
