pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

how to create a multi columns return function ?
3+ messages / 3 participants
[nested] [flat]

* how to create a multi columns return function ?
@ 2003-10-21 13:08  jclaudio@capitol.fr
  0 siblings, 2 replies; 3+ messages in thread

From: jclaudio@capitol.fr @ 2003-10-21 13:08 UTC (permalink / raw)
  To: pgsql-sql

Hi

I'm moving databases from sybase to postgres.
But I have difficulties in creating a postgres equivalent to the sybase 
stored procedures...

Apparently, Postgres functions should work, but the syb stored procedures 
get only one parameter and return several colums

Here's the code I wrote in postgresql :

create function function_name( int ) returns text
AS ' SELECT column1, column2, column3,...,column15
FROM table_name
WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 = 
\'specific_value2 \' '
LANGUAGE 'SQL';

and I get the message error : returns multi columns

I'm wondering too if It's possible to create a view with a parameter if 
functions don't work.

Has anybody faced the same problem ?

I need help

thanks

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: how to create a multi columns return function ?
@ 2003-10-21 18:42  Adam Witney <awitney@sghms.ac.uk>
  parent: jclaudio@capitol.fr
  1 sibling, 0 replies; 3+ messages in thread

From: Adam Witney @ 2003-10-21 18:42 UTC (permalink / raw)
  To: pgsql-sql


Take a look at the section on 'SQL Functions Returning Sets'


http://www.postgresql.org/docs/7.3/static/xfunc-sql.html#AEN31304



> Hi 
> 
> I'm moving databases from sybase to postgres.
> But I have difficulties in creating a postgres equivalent to the sybase stored
> procedures... 
> 
> Apparently, Postgres functions should work, but the syb stored procedures get
> only one parameter and return several colums
> 
> Here's the code I wrote in postgresql :
> 
> create function function_name( int ) returns text
> AS ' SELECT column1, column2, column3,...,column15
> FROM table_name 
> WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 =
> \'specific_value2 \' '
> LANGUAGE 'SQL'; 
> 
> and I get the message error : returns multi columns
> 
> I'm wondering too if It's possible to create a view with a parameter if
> functions don't work.
> 
> Has anybody faced the same problem ?
> 
> I need help 
> 
> thanks



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: how to create a multi columns return function ?
@ 2003-10-21 18:57  Richard Huxton <dev@archonet.com>
  parent: jclaudio@capitol.fr
  1 sibling, 0 replies; 3+ messages in thread

From: Richard Huxton @ 2003-10-21 18:57 UTC (permalink / raw)
  To: jclaudio@capitol.fr; pgsql-sql

On Tuesday 21 October 2003 14:08, jclaudio@capitol.fr wrote:
> Hi
>
> I'm moving databases from sybase to postgres.
> But I have difficulties in creating a postgres equivalent to the sybase
> stored procedures...
>
> Apparently, Postgres functions should work, but the syb stored procedures
> get only one parameter and return several colums
>
> Here's the code I wrote in postgresql :
>
> create function function_name( int ) returns text
> AS ' SELECT column1, column2, column3,...,column15

You've said it's returning "text" whereas it's returning whatever your columns 
are. You'll want to do something like:

CREATE TYPE fn_ret_type AS (
  column1 int4,
  column2 text,
  column3 date,
 ...
);

CREATE FUNCTION function_name(int) RETURNS fn_ret_type ...

If it returns multiple rows you want SETOF fn_ret_type
-- 
  Richard Huxton
  Archonet Ltd



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-10-21 18:57 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-21 13:08 how to create a multi columns return function ? jclaudio@capitol.fr
2003-10-21 18:42 ` Adam Witney <awitney@sghms.ac.uk>
2003-10-21 18:57 ` Richard Huxton <dev@archonet.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox