Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 3EACC2E0053 for ; Fri, 13 Jun 2008 02:48:23 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 03446-09 for ; Fri, 13 Jun 2008 02:48:13 -0300 (ADT) X-Greylist: delayed 00:14:33.976629 by SQLgrey-1.7.6 Received: from mail1.catalyst.net.nz (godel.catalyst.net.nz [202.78.240.40]) by developer.postgresql.org (Postfix) with ESMTP id A63052E0048 for ; Fri, 13 Jun 2008 02:17:10 -0300 (ADT) Received: from 218-101-8-114.xtreme.net.nz ([218.101.8.114] helo=[192.168.55.27]) by mail1.catalyst.net.nz with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.63) (envelope-from ) id 1K71Qm-00088p-1X; Fri, 13 Jun 2008 17:02:32 +1200 Subject: Re: newbie issues with PDO / stored procedures From: Andrew McMillan To: pg 043g9j2g Cc: pgsql-php@postgresql.org In-Reply-To: <4850F066.7070306@gmail.com> References: <4850F066.7070306@gmail.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-IRwd7LFrcrfWjbpje/ea" Organization: Catalyst .Net Ltd Date: Fri, 13 Jun 2008 17:02:07 +1200 Message-Id: <1213333327.18669.172.camel@hippy.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.22.2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.819 tagged_above=0 required=5 tests=MIME_QP_LONG_LINE=1.819 X-Spam-Level: * X-Archive-Number: 200806/5 X-Sequence-Number: 3852 --=-IRwd7LFrcrfWjbpje/ea Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable On Thu, 2008-06-12 at 05:46 -0400, pg 043g9j2g wrote: > Hi! I am new to PHP and Postgres; in the early stages of creating a web=20 > app, just trying to achieve rudimentary results, yet trying to follow=20 > "best practices" -- in the form of using Stored Procedures vs. Dynamic=20 > SQL -- as I do hope to take it live to the scary ol' Internet one day. >=20 > I have a stored procedure (function), "select_user_details", of the form=20 > "SELECT * FROM users;" which returns "SETOF users". >=20 > When I execute my prepared statement, "SELECT=20 > select_user_details([userid])" via PDO, I get a rowset back which holds=20 > all the columns of users as an array in one column. From my experiement=20 > directly in pgAdmin Query tool, I guess that's what you expect when you=20 > return SETOF, but I already miss the simple DSQL I had set up where I=20 > could access returned columns via $row[columname] syntax. >=20 > I messed around with using fetch() but that didn't seem right (more on=20 > that below). Then I took a look at fetchColumn() but I didn't like that=20 > because you address columns by index # instead of column name. Also the=20 > fact that you advance the row pointer with every call to any kind of=20 > fetch*() seems like it isn't well suited to retrieving data from more=20 > than one column. >=20 > Now it looks like my best option in hopes of being able to access=20 > columns by semantically sensible names is to fetchObject() into PHP=20 > objects representing my database business objects. Is this correct?=20 > Suggestions? Alternatives? Insights? I'm not sure this will work in your case, but the normal way to get SETOF returning functions to return rows that look just like real table rows is to define a view with the semantically sensible names and then do your query against the view. CREATE VIEW my_rows AS SELECT col1, col2, col3 FROM setof_returning_function(); Then you will get a normal select output in your program and be able to refer to $row->col1 etc. This doesn't necessarily work so well when you want to hand in a parameter, unless you can hand that in by doing a join of some kind: =EF=BB=BFCREATE VIEW my_rows AS SELECT col1, col2, col3, jointable.pkey_col AS other_key FROM setof_returning_function(jointable.somecol), jointable; Then you would need to do something like: SELECT col1 FROM my_rows WHERE other_key =3D 75 Etc. Hope this helps, Andrew. ------------------------------------------------------------------------- Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 6, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 It is truth which you cannot contradict; you can without any difficulty contradict Socrates. - Plato ------------------------------------------------------------------------- --=-IRwd7LFrcrfWjbpje/ea Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEABECAAYFAkhR/0wACgkQjJA0f48GgBK3ewCghR4783IPMcZvT+8kr9i4nRu5 w2UAoLDqMtJ5wFckFeaP0gaoTVFL+HIv =qgtx -----END PGP SIGNATURE----- --=-IRwd7LFrcrfWjbpje/ea--