From hareldvd@gmail.com Mon May 24 18:33:51 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id AF699632DC4 for ; Mon, 24 May 2010 15:33:51 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 56976-04 for ; Mon, 24 May 2010 18:33:44 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.154]) by mail.postgresql.org (Postfix) with ESMTP id 1CA5363249C for ; Mon, 24 May 2010 15:33:43 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id d23so1967091fga.1 for ; Mon, 24 May 2010 11:33:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:content-type; bh=wLySSzuQpKdvNuNLrmvtGQSR/4GJImAv1ffvso6ZFM0=; b=QHKvV0EgZ2Tz6k2uv5OfMzprUEr+69HtHhta/aorlw1b1SGaIBCQD6pfVnhsKx/2Ep QHeHmEW1577bY9LYNgnlpdd/YZeqbxBoemGkzyZpbLV/Oy3RSg/g4hzSTJM9iIw+qdGj yYZ/XGUrf5/1cbKL9bzmWOCQ42Bb7aid2sbeo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject :content-type; b=KBzSBMjIZgpM4STyhVji/ggdz5/+xNsbbWwJ1rtN+hTBBtjiiOrStAUzE16HYSmMzw wHeq9WXgdOdfV5F7q/HB70jUtGvJkSpUcb+ZSdLotBBYz0rGnx+wXelywhh2VjTLRvLB VWUoVN9OEN07GN5JPcMC2KCLexTF9Y1et5Y/4= Received: by 10.102.211.40 with SMTP id j40mr4488665mug.69.1274726022050; Mon, 24 May 2010 11:33:42 -0700 (PDT) Received: from [192.168.1.3] (bzq-84-109-195-101.red.bezeqint.net [84.109.195.101]) by mx.google.com with ESMTPS id n10sm16537364mue.12.2010.05.24.11.33.39 (version=SSLv3 cipher=RC4-MD5); Mon, 24 May 2010 11:33:41 -0700 (PDT) Message-ID: <4BFAC67F.8050504@gmail.com> Date: Mon, 24 May 2010 21:33:35 +0300 From: David Harel User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.9) Gecko/20100423 Thunderbird/3.0.4 MIME-Version: 1.0 To: postgres sql Subject: User function that returns a set of rows. Content-Type: multipart/alternative; boundary="------------000303050104040704080704" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.01 tagged_above=-5 required=5 tests=BAYES_40=-0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_FILL_THIS_FORM_SHORT=0.01 X-Spam-Level: X-Archive-Number: 201005/110 X-Sequence-Number: 34158 This is a multi-part message in MIME format. --------------000303050104040704080704 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, I'm struggling to write what seed at the time a simple function like: "CREATE FUNCTION foo() RETURNS IDONO as $$ select * from tbl; return (whatever that will give the *); $$ up till now I got this far: CREATE OR REPLACE FUNCTION select_business_types () RETURNS SETOF RECORD AS $body$ DECLARE rec RECORD; -- fields business_type_id business_type_name BEGIN FOR rec IN SELECT * FROM sv_bo_business_types LOOP RETURN NEXT rec; END LOOP; RETURN; END; $body$ LANGUAGE 'plpgsql'; When I tried it from the shell I got a nasty error message about that I am not in an environment to receive a set ??? (can't see it now. Office restrictions). Any idea? -- Regards. David Harel, ================================== Home office +972 77 7657645 Cellular: +972 54 4534502 Snail Mail: Amuka D.N Merom Hagalil 13802 Israel Email: hareldvd@ergolight-sw.com --------------000303050104040704080704 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi,

I'm struggling to write what seed at the time a simple function
like:
"CREATE FUNCTION foo() RETURNS IDONO as
$$
select * from tbl;
return (whatever that will give the *);
$$

up till now I got this far:

CREATE OR REPLACE FUNCTION select_business_types ()

RETURNS SETOF RECORD

$body$

DECLARE

rec RECORD;

-- fields business_type_id business_type_name

BEGIN

FOR

rec IN SELECT * FROM sv_bo_business_types

LOOP

RETURN NEXT rec;

END LOOP;

RETURN;

END;

$body$

LANGUAGE 'plpgsql';

When I tried it from the shell I got a nasty error message about that I am not in an environment to receive a set ??? (can't see it now. Office restrictions).

Any idea?

-- 
Regards.

David Harel,

==================================

Home office +972 77 7657645
Cellular:   +972 54 4534502
Snail Mail: Amuka
            D.N Merom Hagalil
            13802
            Israel
Email:      hareldvd@ergolight-sw.com

--------------000303050104040704080704-- From eggyknap@gmail.com Mon May 24 22:14:57 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 6E029633C51 for ; Mon, 24 May 2010 19:14:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 77249-04 for ; Mon, 24 May 2010 22:14:49 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gy0-f174.google.com (mail-gy0-f174.google.com [209.85.160.174]) by mail.postgresql.org (Postfix) with ESMTP id 8FBEA633A70 for ; Mon, 24 May 2010 19:14:49 -0300 (ADT) Received: by gyg13 with SMTP id 13so1702913gyg.19 for ; Mon, 24 May 2010 15:14:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:cc :subject:references:mime-version:content-type:content-disposition :in-reply-to:user-agent; bh=0bqba77c5JrLtc0fGcdsY1dKPeN0AKsUN+kb2DB14H4=; b=Przf8fir0wICX6WZKqf5ClndS8B0cAWPyaeN/ZFQe+/yRkFbILUJyuT4ecdWWQ6w/2 kwTtc+nwqiaLfnJF1OI72jXPmZOjj1gMzjOoayHgxhZZ1gmw+k1OCYMpFqMPrJGi8VDe qn26kmfjtZ9hsCNrylrik98OdDrwFx1SgLGaI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:cc:subject:references:mime-version :content-type:content-disposition:in-reply-to:user-agent; b=J6yEmAOtnYY5SNR80wf7MM0p3SkNA9gB5yOujeKzmCFptViZi0lwyOAue5qdjpuFrJ 6fV+Kl7f1hid0TTrGlzdFjDICS7W29TKNukU+WpiDBZM3bdRGfeadIGEXlu2rJc5H95V Ewka2x8zbvE9g5Of7c/o8FrnMnLENDq4D6xI0= Received: by 10.101.6.1 with SMTP id j1mr7223708ani.200.1274739288314; Mon, 24 May 2010 15:14:48 -0700 (PDT) Received: from eddie (c-67-182-224-31.hsd1.ut.comcast.net [67.182.224.31]) by mx.google.com with ESMTPS id f7sm18969308anb.17.2010.05.24.15.14.46 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 24 May 2010 15:14:47 -0700 (PDT) Message-ID: <4bfafa57.0752640a.0b22.ffff88ac@mx.google.com> Date: Mon, 24 May 2010 16:14:45 -0600 From: Joshua Tolley To: David Harel Cc: postgres sql Subject: Re: User function that returns a set of rows. References: <4BFAC67F.8050504@gmail.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="kadn00tgSopKmJ1H" Content-Disposition: inline In-Reply-To: <4BFAC67F.8050504@gmail.com> User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201005/111 X-Sequence-Number: 34159 --kadn00tgSopKmJ1H Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Mon, May 24, 2010 at 09:33:35PM +0300, David Harel wrote: > When I tried it from the shell I got a nasty error message about that = I am > not in an environment to receive a set ??? (can't see it now. Office > restrictions). >=20 > Any idea? Your query should say something like "SELECT * FROM select_business_types()= ". You'll get that error if you instead say "SELECT select_business_types()". -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com --kadn00tgSopKmJ1H Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkv6+lUACgkQRiRfCGf1UMN1AACdGEleVFUtUTenlY1ONaVeTyeV +1AAmwQQInkMPwyhD9L7UMwbBEnS9A8D =ixQj -----END PGP SIGNATURE----- --kadn00tgSopKmJ1H--