Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CF15B650E82 for ; Tue, 24 Jun 2008 20:44:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 77636-01 for ; Tue, 24 Jun 2008 20:44:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ag-out-0708.google.com (ag-out-0708.google.com [72.14.246.241]) by postgresql.org (Postfix) with ESMTP id 863B6650E23 for ; Tue, 24 Jun 2008 20:44:09 -0300 (ADT) Received: by ag-out-0708.google.com with SMTP id 31so25405963agc.5 for ; Tue, 24 Jun 2008 16:44:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:cc:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=0knd4ajkt5xf4V7YlTNzKj8xHE+Sz1VVBRe8A51Fpf8=; b=thaxv05GCptaXLr8sMEOKV8rIraRyDFmi7UfuqltQKk3qd52v5IMIAbogtgyQCQV66 mfR5l4IYy74axe6KMKey91blCIs/Neo0ttvAswVMR3hDx7N0cthrIKNyTIfdBVGXiy44 VbqtJ7tF8Tfl0iiVeKded+rpVlLwdmLB6k6lo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; b=nkTtL6WlXPyOknYD1h73WczudpeHaCBIppyvrhSALr+aNQEKYX0gVJaWB6ryEEpvqT mGtCIbBsanxNzQBMJ0deEsrvezcC1wW5h/pEObuGBZIWKmmQsVN3TgoFPgfpADUdDqfF 3ZJcXwA1JfGFBcD70dCZRzOEYa9U16hHSaEQM= Received: by 10.90.71.15 with SMTP id t15mr12570835aga.109.1214351048231; Tue, 24 Jun 2008 16:44:08 -0700 (PDT) Received: from ?192.168.1.100? ( [68.40.217.217]) by mx.google.com with ESMTPS id f60sm8213905pyh.37.2008.06.24.16.44.06 (version=TLSv1/SSLv3 cipher=RC4-MD5); Tue, 24 Jun 2008 16:44:07 -0700 (PDT) Message-ID: <486186CF.80107@gmail.com> Date: Tue, 24 Jun 2008 19:44:15 -0400 From: pg 043g9j2g User-Agent: Thunderbird 2.0.0.14 (Windows/20080421) MIME-Version: 1.0 To: Andrew McMillan CC: pgsql-php@postgresql.org Subject: Re: newbie issues with PDO / stored procedures References: <4850F066.7070306@gmail.com> <1213333327.18669.172.camel@hippy.mcmillan.net.nz> In-Reply-To: <1213333327.18669.172.camel@hippy.mcmillan.net.nz> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200806/6 X-Sequence-Number: 3853 Andrew McMillan wrote: > 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 > > ------------------------------------------------------------------------- > It helped a lot! Thank you! Now I am using DSQL in my application in a form like 'SELECT * FROM my_usp(:param);' and using PDO's bindParam() function to pass in params. So I think I am still achieving the intended security of using SPs against SQL injection by not directly inserting request args in my SQL, as well as being able to pass in params without explicitly creating a VIEW first. I have even had success in joining two parameterized SPs together as in 'SELECT my_usp1.[only a few columns...], my_usp2.[only a few columns...] FROM my_usp1(param) JOIN my_usp2(param) [etc.]'. Am I incurring some kind of inefficiency by not having an execution plan for that JOINed statement, or does PDO->prepare() take care of that? Which brings me to a re-statement of one of my earlier questions, which I still have not found the answer to: Is there any way to / how do i... write a function that returns a subset of columns from each of two JOINed tables? I have accomplished this by defining a VIEW for my desired output schema first and having the function return that type, but, is there any simple "generic" type I can use as a return type to avoid having to do so? Right now I am working in a RAD/prototyping mode and it slows me down a bit to have to formalize everything in that way. Thanks for any assistance!