From: | "Cesar A(dot) K(dot) Grossmann" <cesar(at)rotnet(dot)com(dot)br> |
---|---|
To: | PostgreSQL-General <pgsql-general(at)postgresql(dot)org> |
Subject: | CREATE FUNCTION problem |
Date: | 2000-11-02 19:55:26 |
Message-ID: | 3A01C6AE.AB3992E6@rotnet.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-11-02 20:32:58 | Re: Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?) |
Previous Message | Steve Wolfe | 2000-11-02 19:40:07 | Re: how good is PostgreSQL |