From: Richard Huxton <dev@archonet.com>
To: jclaudio@capitol.fr
To: pgsql-sql@postgresql.org
Subject: Re: how to create a multi columns return function ?
Date: Tue, 21 Oct 2003 19:57:17 +0100
Message-ID: <200310211957.17282.dev@archonet.com> (raw)
In-Reply-To: <OFC6DC4FE3.EDDE1832-ONC1256DC6.004816D3-C1256DC6.00482FCD@capitol.fr>
References: <OFC6DC4FE3.EDDE1832-ONC1256DC6.004816D3-C1256DC6.00482FCD@capitol.fr>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, jclaudio@capitol.fr
Subject: Re: how to create a multi columns return function ?
In-Reply-To: <200310211957.17282.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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