pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (4+ messages)  latest in thread

Message-ID: <1213333327.18669.172.camel@hippy.mcmillan.net.nz>
Permalink:  ../1213333327.18669.172.camel@hippy.mcmillan.net.nz/
Also on:    postgresql.org/message-id/1213333327.18669.172.camel@hippy.mcmillan.net.nz

 · 

reply

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