Beginner problems with functions (Was: Is this the wrong list?)

From: Andreas Tille <tillea(at)rki(dot)de>
To: PostgreSQL SQL <pgsql-sql(at)hub(dot)org>
Cc: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Beginner problems with functions (Was: Is this the wrong list?)
Date: 2000-08-17 14:05:53
Message-ID: Pine.LNX.4.21.0008171239520.10450-100000@wr-linux02.rki.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

On Wed, 16 Aug 2000, Stephan Szabo wrote on pgsql-general(at)postgresql(dot)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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ross J. Reedstrom 2000-08-17 14:47:00 Re: Unimpletmented features
Previous Message Karel Zak 2000-08-17 13:39:09 Re: Regression Tests

Browse pgsql-sql by date

  From Date Subject
Next Message Joerg Hessdoerfer 2000-08-17 14:30:30 Continuous inserts...
Previous Message Jerome Raupach 2000-08-17 13:23:12 optimization in C