Re: [SQL] WITH RECURSION output ordering with trees

pgsql-sql-ow...@postgresql.org wrote: > In article > , > "Philippe Lang" writes: > >> Thanks for your answer. Si there a built-in function that would allow >> generating the sort path based on the value of the lookup column, >> instead of the id, which

Re: [SQL] WITH RECURSION output ordering with trees

pgsql-sql-ow...@postgresql.org wrote: > Philippe Lang, 10.07.2009 11:10: >> Hi, >> >> I'm playing with the new "WITH RECURSIVE" feature of 8.4. I'm trying >> to figure out how to use it with trees. >> >> Here is the test code I use: >

Re: [SQL] WITH RECURSION output ordering with trees

645 | c111 | 2 2 | 823 | c112 | 2 2 | 243 | c113 | 2 1 | 6 | b12| 1 2 | 845 | c121 | 6 2 | 583 | c122 | 6 1 | 9 | b13| 1 2 | 10 | c131 | 9 I guess this is hard to achieve with a "WITH RECURSIVE" call. So my question is now: is the inclusion of "START WITH... CONNECT BY" planned for Postgresql? I read a patch had been developed for Postgresql 8.3: http://www.postgresql-support.de/blog/blog_hans.html Best regards, Philippe Lang -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] WITH RECURSION output ordering with trees

dea how to do that? (without trying to sort on the lookup column, whose values can be random outside this test) Best regards, Philippe Lang -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] "Subquery must return only one column" & query optimization

pgsql-sql-ow...@postgresql.org wrote: > "Philippe Lang" writes: >> I was trying to run this query this morning: > >> -- >> SELECT > >> r.*, > >> ( >> SELECT > >> rl.reminder_heade

[SQL] "Subquery must return only one column" & query optimization

reminders WHERE customer_id = r.customer_id ) -- Thanks, Philippe Lang -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] SQL tree duplication

Postgresql could help in this situation. Regards, Philippe Lang ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Concatenation through SQL

Pavel Stehule wrote: >> SELECT >> appointments.name AS appointment, >> (SELECT COUNT(*) FROM employees AS e where e.appointment_id = >> appointments.id) AS num_employees, >> (SELECT array_to_string(array_accum(name),', ') FROM employees AS e >> WHERE e.appointment_id = appointments.id) AS employe

Re: [SQL] Concatenation through SQL

[EMAIL PROTECTED] wrote: > Philippe Lang <[EMAIL PROTECTED]> schrieb: > > 2 ways: > > * You can use something like this: > > test=*# select * from a; > id | val > +- > 1 | foo > 2 | bar > (2 rows) > > test=*

[SQL] Concatenation through SQL

est we want to concatenate the variable inside, with the separator ', ' inbetween. Thanks for your tips! Philippe Lang ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] Temporal databases

7;2007-06-02'::date; SELECT * from foo;" would return: -- id s -- 2second line 3third line 4fourth line -- I guess it is much easier to imagine than to develop! For sure I've been watching "Back to future" too much when I was younger. Philippe Lang ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Temporal databases

L standard already defined for querying databases temporally? I think I read about a "Time Connector" or something similar for Oracle, a few years ago... Regards, Philippe Lang ---(end of broadcast)--- TIP 9: In versions below 8.0, the plan

Re: [SQL] quote_nullable alternative?

[EMAIL PROTECTED] wrote: > Hi, > > There has been a recent discussion on the hacker mailing-list > recently, regarding a new patch for postgresql, that returns 'NULL' > instead of NULL when a NULL value is passed as a parameter. > > It's exactly what I need on my 8.2.5 server. > > Is there an

[SQL] quote_nullable alternative?

nightmare... Thanks Philippe Lang ---(end of broadcast)--- TIP 6: explain analyze is your friend

[SQL] Temporal databases

Hi, Does anyone have experience, tips, links, regarding how to build temporal databases with Postgresql? Thanks Philippe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if y

Re: [SQL] Table transform query

> A take on a self-join: > > SELECT t1.serial, t1.date as dateL, MIN(t2.date) as dateR FROM t t1 > LEFT JOIN t t2 ON t1.serial = t2.serial AND t1.date < t2.date AND > t2.delivery = 'R' > WHERE t1.delivery = 'L' > GROUP BY t1.serial, t1.date > > Whether this is any clearer, or runs faster, than

Re: [SQL] Table transform query

te and f2.delivery = 'R' order by f2.date asc limit 1 ) as dateR from foo as f where f.delivery = 'L' order by f.serial, f.date - I'm not sure if we could use a self-join here... Cheers, Philippe Lang ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[SQL] Table transform query

Hi, I'm trying to find out how to transform this kind of table data (history of rental data in a firm): date serial delivery -- 1 1 L 1 2 L 1 3 L 2 1 R 2 2 R 2 4 L 3 5 L 3 3 R 3 4 R 4

Re: [SQL] Way to reverse ordering of an IP ... ?

d+)\.(\d+)\.(\d+)\.(\d+) for the match, and \4.\3.\2.\1 for the replacement in your Regexp. Cheers, -- Philippe Lang, Ing. Dipl. EPFL Attik System rte de la Fonderie 2 1700 Fribourg Switzerland http://www.attiksystem.ch Tel: +41 (26) 422 13 75 Fax: +41 (26

[SQL] pgxml & xpath_table

val3 1 C1 L1 1 2 3 1 L2 11 22 33 I was expecting doc_num would receive twice the C1 value, just like with a normal sql join. Regards, -- Philippe Lang, Ing. Dipl. EPFL Attik Syste

[SQL] Concatenate strings using GROUP BY

1 a/b 2 12c I was thinking of something like: SELECT id, fk, CONCAT(str || '/') FROM FOO GROUP BY fk Do we have to use PL/PGSQL for that? Thanks, -- Philippe Lang, Ing. Dipl. EPFL Attik System rte de la Fonderie 2 1700 Fribourg S

Re: [SQL] outer join in ms query

Or add a view to your PG database, and simply issue a select from Excel... That faster and easier to maintain that any code you can write client-side... -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Jeff Eckermann Envoyé : lundi, 18. avril 2005 16:51 À

[SQL] DROP TYPE without error?

automatically, based on data found in a database. Thanks - Philippe Lang ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Postgresql inheritance workaround

Posgresql by the way, which is really great... ------ Philippe Lang Attik System ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Reuse previously calculated column in sql query?

Hello, Is it possible to reuse a previously calculated column in a following column, like: SELECT foo.val1 AS col1, long_calculation(foo.val1) AS col2, COL2 * 0.75 AS col3 FROM foo; instead of writing: SELECT foo.val1 AS col1, long_calculation(foo.val1) AS col2, long_calculation(foo.val1) * 0.7

Re: [SQL] Plperlu function & backticks return value -> truncated?

Thanks! That's perfect... `ps -awfux | grep postgres` runs fine... -Message d'origine- De : Michael Fuhr [mailto:[EMAIL PROTECTED] Envoyé : mercredi, 13. octobre 2004 17:47 À : Philippe Lang Cc : [EMAIL PROTECTED] Objet : Re: [SQL] Plperlu function & backticks return valu

Re: [SQL] Plperlu function & backticks return value -> truncated?

hard Huxton [mailto:[EMAIL PROTECTED] Envoyé : mercredi, 13. octobre 2004 17:25 À : Philippe Lang Cc : [EMAIL PROTECTED] Objet : Re: [SQL] Plperlu function & backticks return value -> truncated? > Is that a known limitation of plperlu? Or maybe something else? Depends if user_type is just

[SQL] Plperlu function & backticks return value -> truncated?

Hello, I'm using the following show_users() function in order to retreive in postgresql the output from the unix "ps" command. When run directly from a shell, this code: $ps = `ps -afux | grep postgres`; @fields = split /\n/, $ps; return "{" . join(",", @fields) . "}"; ... runs fine. But

[SQL] Datetime conversion in WHERE clause

nd simply return a null in case the parameter is not a valid date? Thanks Philippe Lang ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] stored procedures and type of returned result.

Patrice, You might have a look at "views" as well. That's not strictily speaking a stored procedure, but maybe it's what you are searching for? Philippe Lang -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Richard Huxton Envo

[SQL] Disable trigger with Postgresql 7.4.x?

YOUR_TABLE_NAME'; update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname='YOUR_TABLE_NAME'; What is the recommended way of doing that under Postgresql 7.4.x? Thanks Philippe Lang ---(end of broadcast)-

Re: [SQL] function expression in FROM may not refer to other relations of same query level

om ( select id, usr, code, get_lines(code) as get_lines_data from tbl offset 0 ) as ss; --- Philippe Lang -Message d'origine- De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : jeudi, 12. août 2004 16:31 À : Philippe Lang

Re: [SQL] function expression in FROM may not refer to other relations of same query level

> > The problem now is that get_lines is being called twice per line. > > Is get_lines() defined as IMMUTABLE? Should it be? I have tried defining get_lines as "IMMUTABLE", or "WITH (iscachable)", but it keeps on getting called twice per line in the following query... select id, usr, code

Re: [SQL] function expression in FROM may not refer to other relations of same query level

EMAIL PROTECTED] De la part de Philippe Lang Envoyé : mercredi, 11. août 2004 08:41 À : [EMAIL PROTECTED] Objet : Re: [SQL] function expression in FROM may not refer to other relations of same query level Hello, > Whats wrong with just using CASE: > > select id, usr, code, > case when

Re: [SQL] function expression in FROM may not refer to other relations of same query level

Hello, > Whats wrong with just using CASE: > > select id, usr, code, > case when code = 1 then 'A' else 'Z' end as line1, > case when code = 1 then 'A' else 'Z' end as line2 from tbl; The code I showed in my last mail was actually test code only. The logic is more complicated, and I'm n

[SQL] function expression in FROM may not refer to other relations of same query level

Hello, I'm trying to use the ROWTYPE return value of a plpgsql function in a SELECT query. The test code is below. The following query is accepted: select id, usr, code, line1, line2 from tbl, get_lines(1); idusr code line1 line2 -- 1 one 1

[SQL] sub-select parameter problem

x27;m sure there is a better way to do that... Thanks for your help! (And for reading, by the way!) ------- Philippe Lang Attik System ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose

Re: [SQL] crosstabs

e could do that without CASE... END... -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Philippe Lang Envoyé : jeudi, 19. février 2004 17:02 À : [EMAIL PROTECTED] Objet : [SQL] crosstabs Hello, I need to do something similar to a cross tabulation,

[SQL] crosstabs

M1 | M1, D3 ||| M1 ||| M1, D4 | M2 ||| M2, D1 | M3 | M3, D1 ||| M3 | M3, D2 ||| M3 || M3, D3 | | M3 | M3, D4 ||| M3 || M3, D5 |

[SQL] Disable/Enable Trigger?

Hello, How can I disable a trigger in a function, and then enable it again? I'm using PG 7.3.4. Do I have to delete it, and create it again? Thanks Philippe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http:

Re: [SQL] sql query with join and parameter in postgresql function

Thanks! That's perfect. I join in a view, and limit in a function, with a WHERE clause. And no need for any redundant composite type, although I agree this is not that complicated to use... Philippe Lang ---(end of broadcast)--- TIP 6

[SQL] sql query with join and parameter in postgresql function

Hello, I'd like to write a function similar to that: CREATE FUNCTION public.report_join_parameter(char(6)) RETURNS ??? AS ' SELECT tableA.field1, tableA.field2, tableB.field1, tableB.field2, tableC.field1, tableC.field2, FROM tableA INNER JOIN