Message-ID: <468E78FA.6030309@mediamatters.org>
Date: Fri, 06 Jul 2007 13:16:42 -0400
From: Jon Sime <jsime@mediamatters.org>
Organization: Media Matters for America
User-Agent: Thunderbird 2.0.0.4 (X11/20070615)
MIME-Version: 1.0
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: select from table and add rows.
References: <200707061425.09074.gary.stainburn@ringways.co.uk>
	<20070706150257.GS20115@phlogiston.dyndns.org>
	<200707061735.39592.gary.stainburn@ringways.co.uk>
In-Reply-To: <200707061735.39592.gary.stainburn@ringways.co.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Gary Stainburn wrote:
> On Friday 06 July 2007 16:02, Andrew Sullivan wrote:
>> On Fri, Jul 06, 2007 at 02:25:08PM +0100, Gary Stainburn wrote:
>>> This sets up an array with some pseudo values and then populates it with
>>> *proper* values from a table.
>>>
>>> How's the best way to do this all within SQL.
>> I'm obviously not understanding the question properly, because I
>> think you want ot set up a PHP array with values from your table,
>> without using PHP.  Which would of course make no sense.  Do you
>> mean, how do you populate an array data type with data from individual
>> database columns?
>>
>> A
> 
> I want to be able to do away with the first line of the code, and create a 
> select statement that would generate the three rows first, followed by the 
> rows from the table I'm using. I want to be able to do the same job without 
> having to pre-load the array.
> 
> In other words, what's the best way to inject pre-defined rows into a select 
> statement.
> 

You could UNION the real query with a dummy query, where the latter uses 
a literal SELECT against the values you want injected instead of a 
table. Something along the lines of:

     select 'foo' as col1, 'bar' as col2, 'xyz' as col3
     union
     select col1,col2,col3 from [...continue real query...]

There are variations on this theme you could use (such as a "select ... 
from (values ...)" construct), though none of them would really be any 
more pleasing.

If you need those injected values to always be the first record 
returned, it gets a little more involved.

To force the injected values to remain the first record returned, would 
necessitate wrapping your real query up into a subquery (still doing the 
UNION, as well) to keep its order/limit/etc. clauses from interfering 
with the injected values.

And without an order on the final UNION-ed results, the order in which 
you specify the component queries of the UNION does not guarantee the 
order of the results, so you would still need to order at the final 
stage somehow, as well.

Which all basically leads to the question: Is that worth saving one line 
in your PHP code? Particularly given that you'd be adding that, and 
almost certainly a couple more, back in SQL.

-Jon

-- 
Senior Systems Developer
Media Matters for America
http://mediamatters.org/