error using pl/pgsql? [possibly off topic]
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
johnny@halfahead.dk
+45 6315 7328
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?
Show quoted text
COMMIT WORK;
RETURN ret_val;
END;
'
LANGUAGE 'plpgsql';
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 text would 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, it all 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