Обсуждение: Beginner problems with functions (Was: Is this the wrong list?)

On Wed, 16 Aug 2000, Stephan Szabo wrote on pgsql-general@postgresql.org:
(sorry for the crossposting, just to tell the list that I now switched to
the right one hopefully)

> I think the thing is that most people don't have basic examples, they
Perhaps someone knows one nice doc.  I only found some hints for
ma problems in the PGSQL-Part of the Bruce Momjian book.  But
may be PGSQL is in fact the thing I want and so I may possibly stick to
that.  Now here is the first question about that:

web=# create function atTest ( varchar )
web-#   returns bool
web-#   As ' BEGIN
web'#     Select * From Mitarbeiter Where FName = $1 ;
web'#     IF NOT FOUND THEN
web'#       RETURN ''f'' ;
web'#     ELSE
web'#       RETURN ''t'' ;
web'#     END IF ;
web'#   END; '
web-#   language 'plpgsql' ;
CREATE
web=# SELECT attest ( 'Tille' ) ;
ERROR:  unexpected SELECT query in exec_stmt_execsql()
web=#

Could somebody enlighten me, what here goes wrong?

> have whatever things they particularly needed.  However, there
> are a couple defined in the create_function_2 regression test.
Thanks for your hint.  I tried to check these examples, but found that
setof beast is not well documented.

I tested kind of this
> CREATE FUNCTION hobbies(person)
>    RETURNS setof hobbies_r
>    AS 'select * from hobbies_r where person = $1.name'
>    LANGUAGE 'sql';
But it returns just did:


web=# SELECT my_test ( ) ;

 ?column?
-----------
 136437368
 136437368
 136437368
 ...

I had the hope to get the contents of the table like if I would
do 'SELECT * FROM table;'

Also kind of

   RETURNS SETOF varchar
   AS ' SELECT * FROM table ; '

doesn't do the trick, because this is syntactical wrong.

To explain what I'm intendet to do:  I want to port some servlets
from MS-SQL to PostgreSQL.  The servlets contain code like:

    rs = stmt.executeQuery("stored_procedure arg1, arg2");
    while ( rs.next() )
      do_something(rs.getString("col1"), rs.getString("col2"),
                   rs.getString("col3"), rs.getString("col4") );

So I have to serve my servlet with any kind of datasets and I really
can't imagine, that such a basic task isn't possible with PostgeSQL.

Kind regards

         Andreas.

On Thu, 17 Aug 2000, Andreas Tille wrote:

> On Wed, 16 Aug 2000, Stephan Szabo wrote on pgsql-general@postgresql.org:
> (sorry for the crossposting, just to tell the list that I now switched to
> the right one hopefully)
>
> > I think the thing is that most people don't have basic examples, they
> Perhaps someone knows one nice doc.  I only found some hints for
> ma problems in the PGSQL-Part of the Bruce Momjian book.  But
> may be PGSQL is in fact the thing I want and so I may possibly stick to
> that.  Now here is the first question about that:
>
> web=# create function atTest ( varchar )
> web-#   returns bool
> web-#   As ' BEGIN
> web'#     Select * From Mitarbeiter Where FName = $1 ;
> web'#     IF NOT FOUND THEN
> web'#       RETURN ''f'' ;
> web'#     ELSE
> web'#       RETURN ''t'' ;
> web'#     END IF ;
> web'#   END; '
> web-#   language 'plpgsql' ;
> CREATE
> web=# SELECT attest ( 'Tille' ) ;
> ERROR:  unexpected SELECT query in exec_stmt_execsql()
> web=#
>
> Could somebody enlighten me, what here goes wrong?

What you may need to do is declare a variable of type record
and do SELECT INTO <variable> * From ... rather than just
the SELECT.

> > CREATE FUNCTION hobbies(person)
> >    RETURNS setof hobbies_r
> >    AS 'select * from hobbies_r where person = $1.name'
> >    LANGUAGE 'sql';
> But it returns just did:
>
>
> web=# SELECT my_test ( ) ;
>
>  ?column?
> -----------
>  136437368
>  136437368
>  136437368
>  ...
>
> I had the hope to get the contents of the table like if I would
> do 'SELECT * FROM table;'

Yeah, setof <record type> seems fairly wierd.  SETOF  basetype if
you do a SELECT <col> FROM table seems to work though.
I sort of expected that the ones in the regression test would
either do something understandable or at least error if they
are testing for brokenness.