Thread: Passing values to a plpgsql functions

-----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

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