Any SQL-functions examples available

On Mon, 14 Aug 2000, I wrote under the subject "Any SQL-functions examples
available":

I tried to write my first SQL-functions with the intention to
port some stored procedures from MS SQL server. It would make my
task much simpler if someone could point me to some simple
examples which demonstate the use of function parameters in
SELECT/INSERT statements and how to get the results transformed
into reasonable return types, for instance if SELECT was successful
return true else false, etc.

Is there any other list where I could expect answers to those simple
questions. I could not imagine that nobody has ever written some
simple tests with SQL functions. Perhaps this problem is to simple
here.

Or may be I have the wrong philosophy and those functions are not
ment to be used in PostgreSQL?

Kind regards

Andreas.

You'd probably be better off in pgsql-sql@postgresql.org. Also look
under the programmers documention for the interfaces (Specific interface
questions should probably go to pgsql-interfaces@postgresql.org) There
are ways to functions in Sql, PL/PgSQL, TCL etc. etc. It all depends on
what you need to do.

Here is a quick example on writing an sql procedure with parameters:

(create table and add some data)

CREATE FUNCTION get_user_age(int4) RETURNS int4
AS 'select user_age from user_tbl where user_id = $1' LANGUAGE
'sql';

select get_user_age(2);
get_user_age
--------------
13
(1 row)

Chris Ryan

Andreas Tille wrote:

On Wed, 16 Aug 2000, Andreas Tille wrote:

On Mon, 14 Aug 2000, I wrote under the subject "Any SQL-functions examples
available":

I tried to write my first SQL-functions with the intention to
port some stored procedures from MS SQL server. It would make my
task much simpler if someone could point me to some simple
examples which demonstate the use of function parameters in
SELECT/INSERT statements and how to get the results transformed
into reasonable return types, for instance if SELECT was successful
return true else false, etc.

Is there any other list where I could expect answers to those simple
questions. I could not imagine that nobody has ever written some
simple tests with SQL functions. Perhaps this problem is to simple
here.

Or may be I have the wrong philosophy and those functions are not
ment to be used in PostgreSQL?

I think the thing is that most people don't have basic examples, they
have whatever things they particularly needed. However, there
are a couple defined in the create_function_2 regression test.

CREATE FUNCTION hobbies(person)
RETURNS setof hobbies_r
AS 'select * from hobbies_r where person = $1.name'
LANGUAGE 'sql';
CREATE FUNCTION hobby_construct(text, text)
RETURNS hobbies_r
AS 'select $1 as name, $2 as hobby'
LANGUAGE 'sql';
CREATE FUNCTION equipment(hobbies_r)
RETURNS setof equipment_r
AS 'select * from equipment_r where hobby = $1.name'
LANGUAGE 'sql';
CREATE FUNCTION user_relns()
RETURNS setof name
AS 'select relname
from pg_class
where relname !~ ''pg_.*'' and
relkind <> ''i'' '
LANGUAGE 'sql';

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.