CREATE FUNCTION problem
От | Cesar A. K. Grossmann |
---|---|
Тема | CREATE FUNCTION problem |
Дата | |
Msg-id | 3A01C6AE.AB3992E6@rotnet.com.br обсуждение исходный текст |
Ответы |
Re: CREATE FUNCTION problem
|
Список | 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/
В списке pgsql-general по дате отправления: