Обсуждение: Any SQL-functions examples available

Hello,

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.

Thanks for any halp to a bloody PostgreSQL-beginner like me

         Andreas.

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

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.