Re: newbie issues with PDO / stored procedures

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

-------------------------------------------------------------------------

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkhR/0wACgkQjJA0f48GgBK3ewCghR4783IPMcZvT+8kr9i4nRu5
w2UAoLDqMtJ5wFckFeaP0gaoTVFL+HIv
=qgtx
-----END PGP SIGNATURE-----