Re: Passing values to a plpgsql functions

Hi Dave,

Thanks for the answer but I do not think this is my case. This is what
I am doing:

The functions I am creating is:

CREATE OR REPLACE FUNCTION getthemedescription (text) RETURNS text AS
'
DECLARE
themeid ALIAS FOR $1;
metaid metadata.meta_id%TYPE;
generalid general.general_id%TYPE;
descriptionidcursor CURSOR FOR SELECT description_id FROM
general_description WHERE general_id = generalid;

BEGIN
SELECT INTO metaid meta_id FROM themehasmetadata WHERE theme_id
= themeid;
SELECT INTO generalid general_id FROM meta_general WHERE meta_id
= metaid;
OPEN descriptionidcursor;

RETURN "descriptionidcursor"
END;
' LANGUAGE 'plpgsql';

I call this function in the following way:

String themeID = "mathematics";
String queryString = "SELECT getthemedescription(" + themeID +
")");

Statement stmt = conn.createStatement();

ResultSet l_cursorRSet = stmt.executeQuery(queryString);

.......

If I send the themeID as above I get: ERROR: Attribute 'mathematics' not
found.

If I send the themeID = "'mathematics'" I get: fmgr_info: function
24907: cache lookup failed.

I think it has something to do with the quotes, but what?

Best regards

Jose Luis

> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-
> owner(at)postgresql(dot)org] On Behalf Of Dave Cramer
> Sent: viernes, 01 de marzo de 2002 14:39
> To: 'Jose Luis LG'; pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] Passing values to a plpgsql functions
>
> Jose,
>
> Postgres is slightly different than most databases in it's
> implementation of functions. What you need to do is put the function
in
> a select as follows:
>
> there is a builtin function called nextval('sequence') so you can
"call"
> nextval like this
>
> select nextval('sequence_name')
>
> Note: the single quotes are necessary and it will return a column
> nextval in the resultset.
>
> Dave
> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Jose Luis LG
> Sent: Friday, March 01, 2002 4:18 AM
> To: pgsql-jdbc(at)postgresql(dot)org
> Subject: [JDBC] Passing values to a plpgsql functions
>
>
> Hello,
>
> Can anybody help me? How can I pass values to a plpgsql functions
from
> jdbc. I have tried the CallableStatement but this seems not to be
> implemented (Using Postgresql 7.2). Is there any way of passing the
> values via Statement or PreparedStatement. If this is not possible
> could someone tell me where I can find some examples doing this via
the
> FastPath Api.
>
>
> Thanks and best regards
>
>
>
> Jose Luis