From: Andrew McMillan <andrew@catalyst.net.nz>
To: pg 043g9j2g <pg043g9j2g@gmail.com>
Cc: pgsql-php@postgresql.org
Subject: Re: newbie issues with PDO / stored procedures
Date: Fri, 13 Jun 2008 17:02:07 +1200
Message-ID: <1213333327.18669.172.camel@hippy.mcmillan.net.nz> (raw)
In-Reply-To: <4850F066.7070306@gmail.com>
References: <4850F066.7070306@gmail.com>
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
> app, just trying to achieve rudimentary results, yet trying to follow
> "best practices" -- in the form of using Stored Procedures vs. Dynamic
> SQL -- as I do hope to take it live to the scary ol' Internet one day.
>
> I have a stored procedure (function), "select_user_details", of the form
> "SELECT * FROM users;" which returns "SETOF users".
>
> When I execute my prepared statement, "SELECT
> select_user_details([userid])" via PDO, I get a rowset back which holds
> all the columns of users as an array in one column. From my experiement
> directly in pgAdmin Query tool, I guess that's what you expect when you
> return SETOF, but I already miss the simple DSQL I had set up where I
> could access returned columns via $row[columname] syntax.
>
> I messed around with using fetch() but that didn't seem right (more on
> that below). Then I took a look at fetchColumn() but I didn't like that
> because you address columns by index # instead of column name. Also the
> fact that you advance the row pointer with every call to any kind of
> fetch*() seems like it isn't well suited to retrieving data from more
> than one column.
>
> Now it looks like my best option in hopes of being able to access
> columns by semantically sensible names is to fetchObject() into PHP
> objects representing my database business objects. Is this correct?
> 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:
CREATE 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 = 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
-------------------------------------------------------------------------
Attachments:
signature.asc (application/pgp-signature, 196B) download
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: andrew@catalyst.net.nz, pg043g9j2g@gmail.com
Subject: Re: newbie issues with PDO / stored procedures
In-Reply-To: <1213333327.18669.172.camel@hippy.mcmillan.net.nz>
References: <4850F066.7070306@gmail.com> <1213333327.18669.172.camel@hippy.mcmillan.net.nz>
* 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