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@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Dave Cramer
> Sent: viernes, 01 de marzo de 2002 14:39
> To: 'Jose Luis LG'; pgsql-jdbc@postgresql.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@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Jose Luis LG
> Sent: Friday, March 01, 2002 4:18 AM
> To: pgsql-jdbc@postgresql.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