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/
