Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A923D9F93E2 for ; Wed, 6 Dec 2006 07:26:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 50325-07 for ; Wed, 6 Dec 2006 07:26:09 -0400 (AST) X-Greylist: delayed 00:24:19.292149 by SQLgrey-1.7.4 Received: from mailhub1.sghms.ac.uk (mailhub1.sghms.ac.uk [194.82.51.7]) by postgresql.org (Postfix) with ESMTP id 39C999F9F7B for ; Wed, 6 Dec 2006 07:26:13 -0400 (AST) Received: from [194.82.51.24] (helo=imail) by mailhub1.sghms.ac.uk with esmtp (Exim 4.60) (envelope-from ) id 1GruWr-0006k8-MS; Wed, 06 Dec 2006 11:01:33 +0000 Received: from [172.16.20.3] (mrc1-003.sghms.ac.uk [172.16.20.3]) by imail.sghms.ac.uk (iPlanet Messaging Server 5.2 HotFix 2.10 (built Dec 26 2005)) with ESMTPA id <0J9U00GRRMMLE4@imail.sghms.ac.uk>; Wed, 06 Dec 2006 11:01:33 +0000 (GMT) Date: Wed, 06 Dec 2006 11:02:29 +0000 From: Adam Witney Subject: Re: PLPGSQL - PHP data querying In-reply-to: <938994.73369.qm@web8504.mail.in.yahoo.com> To: Ganesh Laxmanmurthy Cc: pgsql-php@postgresql.org Message-id: <4576A345.6040109@sgul.ac.uk> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: 7BIT User-Agent: Thunderbird 1.5.0.8 (Macintosh/20061025) X-Enigmail-Version: 0.94.0.0 References: <938994.73369.qm@web8504.mail.in.yahoo.com> X-SGUL-MailScanner-Information: Please contact the ISP for more information X-SGUL-MailScanner-MH1: Found to be clean X-SGUL-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=-1.44, required 5, autolearn=not spam, ALL_TRUSTED -1.44) X-SGUL-MailScanner-From: awitney@sgul.ac.uk X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=BAYES_50, SPF_PASS X-Spam-Level: X-Archive-Number: 200612/13 X-Sequence-Number: 3681 Ganesh Laxmanmurthy wrote: > Dear All: > > Regarding PHP & PLPGSQL Programming. I am new to > PLPGSQL. > > I wish someone suggest me the solution for the below > requirement even though it is very simple as I am new > to the subject and could not find an answer which I > can understand. > > I have a table by name 'users' with several columns. > > I want to query "select * from users' from PHP to > Postgresql through PLPGSQL. > > Therefore I need > > 1. The practical syntax for the PLPGSQL function > > 2. The practical syntax for calling and displaying of > the returned recordset from PHP. > > With the above I would be able to develop futher > programs. > > I would be thankful for the same. here is some untested code, but will give one way of doing it (note there is also no great error checking in it): CREATE TABLE users (id int, name text); CREATE OR REPLACE FUNCTION my_function() RETURNS SETOF record AS ' DECLARE _record record; BEGIN FOR _record IN SELECT * FROM users LOOP RETURN NEXT _record; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; ############################# PHP code ############################# $connstr = "dbname=".$dbname." host=".$host." port=".$port." user=".$user." password=".$passwd; $dbh = pg_connect($connstr); $sql = "SELECT * FROM my_function() AS (id int, name text);"; if($stat = pg_exec($dbh, $sql);) { if($rows = pg_numrows($stat)) { for($i=0; $i < $rows; $i++) { $data = pg_fetch_array($stat, $i); echo $data['id']." -- ".$data['name']."
"; } } } -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.