From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Andreas Tille <tillea(at)rki(dot)de> |
Cc: | PostgreSQL SQL <pgsql-sql(at)hub(dot)org>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [SQL] Beginner problems with functions (Was: Is this the wrong list?) |
Date: | 2000-08-17 15:49:30 |
Message-ID: | Pine.BSF.4.10.10008170833050.96893-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
On Thu, 17 Aug 2000, Andreas Tille wrote:
> 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?
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Barry Hill | 2000-08-17 16:22:20 | Re[2]: Search (select) options // Max SQL length? |
Previous Message | Stephan Szabo | 2000-08-17 15:30:39 | Re: Regression Tests |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2000-08-17 16:15:24 | Re: Continuous inserts... |
Previous Message | Joerg Hessdoerfer | 2000-08-17 15:30:24 |