pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect from table and add rows.
5+ messages / 3 participants
[nested] [flat]
* select from table and add rows.
@ 2007-07-06 13:25 Gary Stainburn <gary.stainburn@ringways.co.uk>
2007-07-06 15:02 ` Re: select from table and add rows. Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Gary Stainburn @ 2007-07-06 13:25 UTC (permalink / raw)
To: pgsql-sql
Hi folks.
In PHP I often do things such as:
$wpartners=array('all'=>'All','sel'=>'Current','mine'=>'Mine');
foreach(
loadhash("select p_id as key, p_name as value
from partners order by p_name"
)
as $key=>$dets) {
$wpartners[$key]=$dets;
}
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.
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select from table and add rows.
2007-07-06 13:25 select from table and add rows. Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2007-07-06 15:02 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-06 16:35 ` Re: select from table and add rows. Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Andrew Sullivan @ 2007-07-06 15:02 UTC (permalink / raw)
To: pgsql-sql
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
--
Andrew Sullivan | ajs@crankycanuck.ca
However important originality may be in some fields, restraint and
adherence to procedure emerge as the more significant virtues in a
great many others. --Alain de Botton
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select from table and add rows.
2007-07-06 13:25 select from table and add rows. Gary Stainburn <gary.stainburn@ringways.co.uk>
2007-07-06 15:02 ` Re: select from table and add rows. Andrew Sullivan <ajs@crankycanuck.ca>
@ 2007-07-06 16:35 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2007-07-06 16:53 ` Re: select from table and add rows. Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-06 17:16 ` Re: select from table and add rows. Jon Sime <jsime@mediamatters.org>
0 siblings, 2 replies; 5+ messages in thread
From: Gary Stainburn @ 2007-07-06 16:35 UTC (permalink / raw)
To: pgsql-sql
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.
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select from table and add rows.
2007-07-06 13:25 select from table and add rows. Gary Stainburn <gary.stainburn@ringways.co.uk>
2007-07-06 15:02 ` Re: select from table and add rows. Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-06 16:35 ` Re: select from table and add rows. Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2007-07-06 16:53 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2007-07-06 16:53 UTC (permalink / raw)
To: pgsql-sql
On Fri, Jul 06, 2007 at 05:35:39PM +0100, Gary Stainburn wrote:
>
> 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.
Oh, I see.
> In other words, what's the best way to inject pre-defined rows into a select
> statement.
Probably a UNION statement. Something like
SELECT 'something'::text as a, 'else'::text as b, 'and other'::text as c UNION
SELECT a::text, b::text, c::text FROM sometable WHERE [criteria]
The casts might not be needed, of course.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select from table and add rows.
2007-07-06 13:25 select from table and add rows. Gary Stainburn <gary.stainburn@ringways.co.uk>
2007-07-06 15:02 ` Re: select from table and add rows. Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-06 16:35 ` Re: select from table and add rows. Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2007-07-06 17:16 ` Jon Sime <jsime@mediamatters.org>
1 sibling, 0 replies; 5+ messages in thread
From: Jon Sime @ 2007-07-06 17:16 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-07-06 17:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-06 13:25 select from table and add rows. Gary Stainburn <gary.stainburn@ringways.co.uk>
2007-07-06 15:02 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-06 16:35 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2007-07-06 16:53 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-07-06 17:16 ` Jon Sime <jsime@mediamatters.org>
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