CREATE FUNCTION problem

Started by Cesar A. K. Grossmannover 25 years ago2 messagesgeneral
Jump to latest
#1Cesar A. K. Grossmann
cesar@rotnet.com.br

Hi!

I'm trying to use pgaccess to create a function that returns the same
tuples the query bellow:

select codigo, nome, 'T' as selected
from cad_exportacao
where codigo in (
select codigo
from user_codigo
where
username = 'cesar' and
tipo = 'E' )
union all
select codigo, nome, 'F' as selected
from cad_exportacao
where codigo not in (
select codigo
from user_codigo
where
username = 'cesar' and
tipo = 'E' )

I need only to pass 'username', so I tried to define a funcion as
bellow:

Name: vinculoExportacao
Parameters: varchar
Returns: setof
Language: sql

Definition:

select codigo, nome, 'T' as selected
from cad_exportacao
where codigo in (
select codigo
from user_codigo
where
username = $1 and
tipo = 'E' )
union all
select codigo, nome, 'F' as selected
from cad_exportacao
where codigo not in (
select codigo
from user_codigo
where
username = $1 and
tipo = 'E' )

When I try to save the function, I get an error message that says:

PostgreSQL error message: ERROR: parser: parse erro at or near "as"
PostgerSQL status: PGRES_FATAL_ERROR

The query pgaccess tries to execute (I deduce it from the error window)
is:

create function
vinculoExportacao (varchar)
returns setof
as 'select codigo, nome, ''T'' as selected
from cad_exportacao
where codigo in ( select codigo
from user_codigo
where username = $1 and tipo = ''E'' )
union all
select codigo, nome, ''F'' as selected
from cad_exportacao
where codigo not in ( select codigo
from user_codigo
where username = $1 and tipo = ''E'' )'
language 'sql'

If I create a file with this SQL instruction, and run it with the '\i'
command, I get the following error message:

# \i tmp/teste2.sql
psql:tmp/teste2.sql:15: ERROR: parser: parse error at or near "as"

(for the ones that knows the same I know, it is an outer join, as
explained in chapter 8, page 101 of the book "PostgreSQL - Introduction
and Concepts", from Bruce Momjian - an excellent book)

Can someone points me where the error is (I really don't fear to appear
to be a fool...)?

TIA
P.S.: sorry the bad english, I'm from Brazil.
--
C�sar A. K. Grossmann
http://members.xoom.com/ckant/

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Cesar A. K. Grossmann (#1)
Re: CREATE FUNCTION problem

"Cesar A. K. Grossmann" <cesar@rotnet.com.br> writes:

create function
vinculoExportacao (varchar)
returns setof
as 'select codigo, nome, ''T'' as selected
from cad_exportacao
...
psql:tmp/teste2.sql:15: ERROR: parser: parse error at or near "as"

setof *what* ? The parser is expecting SETOF typename. In this case
you are going to need a tuple type that corresponds to the row structure
you want to return a set of.

BTW, please see my post from a week or two back that describes the known
problems with functions returning tuples and sets ...

regards, tom lane