Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 94D6B9F9654
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  9 Nov 2007 16:36:18 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 68961-01-6 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  9 Nov 2007 16:36:02 -0400 (AST)
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.186])
	by postgresql.org (Postfix) with ESMTP id 271229F95B9
	for <pgsql-sql@postgresql.org>; Fri,  9 Nov 2007 16:35:23 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id f5so474542nfh
	for <pgsql-sql@postgresql.org>; Fri, 09 Nov 2007 12:35:20 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	bh=9MNE9+9rT1DWzPdJSlPqgqkj9K0UpsMdv0kg9K71+8g=;
	b=pp5/VaieYpZX4bVYclAvXdgnny/CvGPuanTQaJrNlsQztsFJKEhJ3utv+6HNAvvN9H6VwOBudBoXUKTKWJB82I+jP3yAGfz2a3ZlPvTiYWmqRMneTOQc/8RVBaj1O4RrLTT+tI53VqS52Q+SX1Xep0xgLw6MTQzluFyXFJB4Vx8=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=DH6xC8HdV3XzsekvAm8pyI+X9uOOBx1rwe74eV/5CtUHtMxIWNKcOy2ghsBlFY5AeqqWEw2o36NxnU5YPg5anCW2rbBDDWCbPsO8YimXQZelWl7ml/MiTdTiKL/uMaUQTNfp1JHFdfFZHHFKFgCrOjB63jG6hqcU7xkRXovUplw=
Received: by 10.78.201.10 with SMTP id y10mr2998308huf.1194640520444;
	Fri, 09 Nov 2007 12:35:20 -0800 (PST)
Received: by 10.78.35.6 with HTTP; Fri, 9 Nov 2007 12:35:20 -0800 (PST)
Message-ID: <162867790711091235i7e77abcch27731d91285949b6@mail.gmail.com>
Date: Fri, 9 Nov 2007 21:35:20 +0100
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Michele Petrazzo - Unipex srl" <michele.petrazzo@unipex.it>
Subject: Re: functions are returns columns
Cc: Pgsql-Sql <pgsql-sql@postgresql.org>
In-Reply-To: <4734B921.5050704@unipex.it>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <4734B921.5050704@unipex.it>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/37
X-Sequence-Number: 29565

On 09/11/2007, Michele Petrazzo - Unipex srl <michele.petrazzo@unipex.it> wrote:
> Hi all.
> I want that a function return a table rows (like the doc says at 33.4.4.
> SQL Functions as Table Sources), but I want the a function return only a
> few cols, so the same that I select into the func.
> Modifying the doc example:
>
> CREATE TABLE foo (fooid int, foosubid int, fooname text);
> INSERT INTO foo VALUES (1, 1, 'Joe');
> INSERT INTO foo VALUES (1, 2, 'Ed');
> INSERT INTO foo VALUES (2, 1, 'Mary');
>
> CREATE FUNCTION getfoo(int) RETURNS foo AS $$
>      SELECT fooid, foosubid FROM foo WHERE fooid = $1;
> $$ LANGUAGE SQL;
>
> This give me an error:
>
> ERROR:  return type mismatch in function declared to return foo
> DETAIL:  Final SELECT returns too few columns.
> CONTEXT:  SQL function "getfoo"
>
>

CREATE FUNCTION getfoo(int) RETURNS foo AS $$
      SELECT fooid, foosubid FROM foo WHERE fooid = $1 LIMIT 1;
$$ LANGUAGE SQL;

or

 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
      SELECT fooid, foosubid FROM foo WHERE fooid = $1;
 $$ LANGUAGE SQL;

try:
SELECT * FROM getfoo(1);

Regards

Pavel Stehule



> So, how do it?
>
> Thanks,
> Michele
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>