CREATE FUNCTION problem

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/

Responses

Browse pgsql-general by date

  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