[SQL] select from table and add rows.
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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Quote problem
Consider a perl function like this
CREATE OR REPLACE FUNCTION public.test(text) RETURNS VOID AS
$body$
my $var = shift;
spi_exec_query("INSERT INTO tbltest(field1) VALUES ('$var')");
$body$
LANGUAGE 'plperlu' VOLATILE;
and a table
CREATE TABLE tbltest (
field1 text
) WITHOUT OIDS;
When I call the function like
SELECT test('Do this')
it works. But when I call it like
SELECT test('Don't do that')
it fails (of course). So I call it like
SELECT test('Don''t do that')
It still fails and I do understand why. But how do I solve it in the function?
Using a prepared statement would be a solution, like
$sel = spi_prepare('SELECT test($1)', 'text');
spi_exec_prepared($sel, $var);
But I need to get it solved without prepared statements.
What is the best approach considering that $var could contain both
single and double quotes?
Thanks
Re: [SQL] select from table and add rows.
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 | [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Iterate and write a previous row to a temp table?
chester c young wrote: --- Bob Singleton <[EMAIL PROTECTED]> wrote: Revisiting a Time In Status query I received help on - I'm trying to narrow down a subset of data I return for analysis. Given a statusLog as entityId, statusId, timestamp that might look something like entityId | statusId | timestamp 001 | HLD | 2007-06-14 11:07:35.93 001 | RDY | 2007-06-15 11:07:35.93 001 | USE | 2007-06-16 11:07:35.93 001 | RDY | 2007-06-17 11:07:35.93 001 | MNT | 2007-06-18 11:07:35.93 I need to pull for a given span of time - say 2007-06-16 00:00:00.01 (let me call it startTime) to 2007-06-17 23:59:59.99 (call it endTime) in such a way that rows with a timestamp between startTime and endTime AND the latest record prior to or equal to startTime are returned. In the above simplified example, only the second and third rows would be returned. A colleague suggested a temp table, but I'm unsure how to iterate until I pass the startTime and then write the _previous_ and all subsequent rows to a temp table, stopping when I pass the endTime parameter. Any hints? Thanks! Bob Singleton couldn't you use the simple query: select * from sometable where timestamp between (select max(timestamp) from sometable where timestamp <= minTime) and maxTime This works very well unless I have no records where timestamp <= minTime. If I try select max(timestamp) from sometable where timestamp <= minTime; I get max - (1 row) And the blank row/line/value/? confuses the between call - I get 0 rows returned. (if minTime is later than at least one row's timestamp I will see something like) max 2007-06-08 17:42:00.18 (1 row) Any help greatly appreciated! bs
Re: [SQL] select from table and add rows.
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] select from table and add rows.
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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select from table and add rows.
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 | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
