Postgres Pro
Downloads
Home   >   mailing lists

Thread: error using pl/pgsql? [possibly off topic]

error using pl/pgsql? [possibly off topic]

From
"Johnny Jørgensen"
Date:
I'm not sure if this is the place to get help on specific questions, but I'll give it a go..
 
I have a problem with this function:
 
CREATE FUNCTION new_order(text,text)
RETURNS text
AS '
 DECLARE
  p_id ALIAS FOR $1;
  p_date ALIAS FOR $2;
  ret_val text;
 BEGIN
  BEGIN WORK;
   INSERT INTO ordre (person_id,placement_date,status) VALUES (p_id::int4,p_date::decimal,1);
   SELECT currval(ordre_id_seq::text) INTO ret_val;
  COMMIT WORK;
  RETURN ret_val;
 END;
'
LANGUAGE 'plpgsql';
 
- I can create it, but when i run it, I get:
 
NOTICE: plpgsql: ERROR during compile of new_order near line 12
ERROR: parse error at or near ""
 
- the error message not being very informative, I've stared blindly at the script for an hour, messed with it, but i'm not really getting anywhere :/
 
If anyone can point me to my probably embarassingly obvious error, i'd be grateful.
Also, i'd appreciate some pointers to resources on plpgsql -i've got Momjian's book, and "Beginning databases with postgresql" (Stones, Matthew), but they each devote some 10 pages to plpgsql, and i'd love to get around a bit more?
 
thanks,
 
 
 
 

Johnny J�rgensen
 
+45 6315 7328

Re: error using pl/pgsql? [possibly off topic]

From
Stephan Szabo
Date:
On Fri, 23 Nov 2001, [ISO-8859-1] "Johnny J�rgensen" wrote:

A few notes **ed below.

> CREATE FUNCTION new_order(text,text)
> RETURNS text
> AS '
>  DECLARE
>   p_id ALIAS FOR $1;
>   p_date ALIAS FOR $2;
>   ret_val text;
>  BEGIN
>   BEGIN WORK;
** can't put transaction statements in I think to start.
** Take out the begin work and commit work.

>    INSERT INTO ordre (person_id,placement_date,status) VALUES (p_id::int4,p_date::decimal,1);
** I don't think there's a text->decimal conversion defined.  Why is the
** parameter defined as text anyway?

>    SELECT currval(ordre_id_seq::text) INTO ret_val;
** I think you really want ''ordre_id_eq'' since otherwise
** its looking for a parameter or column name,
** and do you really want this back as text rather than
** the integer type currval normally returns?

>   COMMIT WORK;
>   RETURN ret_val;
>  END;
> '
> LANGUAGE 'plpgsql';

Re: error using pl/pgsql? [possibly off topic]

From
"Johnny J\xF8rgensen"
Date:
Thank you,

you were right, transactions can't be inside functions (Docs paragraph 24.2.1. Structure of PL/pgSQL),

concerning the input/output, it's coming in from PHP (from forms) , and for safety's sake, all form input is quoted, so
textwould be better. 
I could return whatever, PHP will typecast anyway, but text in -- text out seemed consistent :)

Anyways, i'm not entirely sure how my input is handled, but when i remove all typecasting, as well as the transactions,
itall makes sense :) 

- regards,

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

On 23-11-2001 at 10:58 Stephan Szabo wrote:

>On Fri, 23 Nov 2001, [ISO-8859-1] "Johnny J�rgensen" wrote:
>
>A few notes **ed below.
>
>> CREATE FUNCTION new_order(text,text)
>> RETURNS text
>> AS '
>>  DECLARE
>>   p_id ALIAS FOR $1;
>>   p_date ALIAS FOR $2;
>>   ret_val text;
>>  BEGIN
>>   BEGIN WORK;
>** can't put transaction statements in I think to start.
>** Take out the begin work and commit work.
>
>>    INSERT INTO ordre (person_id,placement_date,status) VALUES
>(p_id::int4,p_date::decimal,1);
>** I don't think there's a text->decimal conversion defined.  Why is the
>** parameter defined as text anyway?
>
>>    SELECT currval(ordre_id_seq::text) INTO ret_val;
>** I think you really want ''ordre_id_eq'' since otherwise
>** its looking for a parameter or column name,
>** and do you really want this back as text rather than
>** the integer type currval normally returns?
>
>>   COMMIT WORK;
>>   RETURN ret_val;
>>  END;
>> '
>> LANGUAGE 'plpgsql';


Johnny J�rgensen

johnny@halfahead.dk
+45 6315 7328