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

2004-01-16 Thread Philippe Lang 
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 tableB
ON tableA.pk = tableB.fk

INNER JOIN tableC
ON tableB.pk = tableC.fk

WHERE tableC.field3= 0
AND tableB.field1= $1
'
  LANGUAGE 'sql' VOLATILE;


Do I ***HAVE TO*** define a composite type for the columns my function
is supposed to return? Isn't there a simpler method, that could allow me
to avoid this extra work?

I thought a view would be more appropriate, but if I'm not wrong, a view
does not accept any parameter as input...

Thanks!

Philippe

---(end of broadcast)---
TIP 8: explain analyze is your friend

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

2004-01-16 Thread Philippe Lang 
> > I thought a view would be more appropriate, but
> > if I'm not wrong, a view does not accept any parameter
> > as input...
> 
> But you can just define the view and select it with a WHERE clause
with
> your parameter, just like a normal table. Would that help?

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: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Disable/Enable Trigger?

2004-02-09 Thread Philippe Lang 
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://archives.postgresql.org

[SQL] crosstabs

2004-02-19 Thread Philippe Lang 
Hello,

I need to do something similar to a cross tabulation, but without any
aggregation.

Here is an example:


--  TABLE DEFINITION
 
CREATE TABLE public.type
(
  code char(1) PRIMARY KEY,
  type varchar(30)
) WITHOUT OIDS; 
 
CREATE TABLE public.master
(
  id int4 PRIMARY KEY,
  master_name varchar(30)
) WITHOUT OIDS;

CREATE TABLE public.detail
(
  id int4 PRIMARY KEY,
  code_type char(1) REFERENCES public.type,
  id_master int4 REFERENCES public.master,
  detail_name varchar(30)
) WITHOUT OIDS; 
 

--  DATA
 
INSERT INTO public.type VALUES('A', 'TA'); 
INSERT INTO public.type VALUES('B', 'TB'); 
INSERT INTO public.type VALUES('C', 'TC'); 
 
INSERT INTO public.master VALUES(1, 'M1'); 
INSERT INTO public.master VALUES(2, 'M2'); 
INSERT INTO public.master VALUES(3, 'M3'); 
 
INSERT INTO public.detail VALUES(1,  'A', 1, 'M1, D1'); 
INSERT INTO public.detail VALUES(2,  'B', 1, 'M1, D2'); 
INSERT INTO public.detail VALUES(3,  'A', 1, 'M1, D3'); 
INSERT INTO public.detail VALUES(4,  'C', 1, 'M1, D4'); 
INSERT INTO public.detail VALUES(5,  'C', 2, 'M2, D1'); 
INSERT INTO public.detail VALUES(6,  'A', 3, 'M3, D1'); 
INSERT INTO public.detail VALUES(7,  'A', 3, 'M3, D2'); 
INSERT INTO public.detail VALUES(8,  'B', 3, 'M3, D3'); 
INSERT INTO public.detail VALUES(9,  'A', 3, 'M3, D4'); 
INSERT INTO public.detail VALUES(10, 'B', 3, 'M3, D5'); 
INSERT INTO public.detail VALUES(11, 'C', 3, 'M3, D6'); 
INSERT INTO public.detail VALUES(12, 'C', 3, 'M3, D7'); 
 

--  QUERY
 
SELECT 
  master_name, 
  detail_name, 
  type 
 
FROM 
  master INNER JOIN detail 
  ON master.id = detail.id_master 
 
  INNER JOIN type 
  ON detail.code_type = type.code 
 
ORDER by master.id, detail.id; 

 


The result of that is:

--
master_name | detail_name | type |
--
M1  | M1, D1  | TA   |
M1  | M1, D2  | TB   |
M1  | M1, D3  | TA   |
M1  | M1, D4  | TC   |
M2  | M2, D1  | TC   |
M3  | M3, D1  | TA   |
M3  | M3, D2  | TA   |
M3  | M3, D3  | TB   |
M3  | M3, D4  | TA   |
M3  | M3, D5  | TB   |
M3  | M3, D6  | TC   |
M3  | M3, D7  | TC   |
--


I need something like this:


master_name | TA | TB | TC |

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



Does anyone know how to do that in Postgresql? I run version 7.3.4.

Thanks for any idea you might have.

Philippe Lang



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] crosstabs

2004-02-19 Thread Philippe Lang 
Here is a solution:


--  QUERY

SELECT
  master_name,
  CASE WHEN type = 'TA' THEN detail_name END as TA,
  CASE WHEN type = 'TB' THEN detail_name END as TB,
  CASE WHEN type = 'TC' THEN detail_name END as TC

FROM
  master INNER JOIN detail
  ON master.id = detail.id_master

  INNER JOIN type
  ON detail.code_type = type.code

ORDER by master.id, detail.id;



I'm not sure if we 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, but without any aggregation.

Here is an example:


--  TABLE DEFINITION

CREATE TABLE public.type
(
  code char(1) PRIMARY KEY,
  type varchar(30)
) WITHOUT OIDS; 
 
CREATE TABLE public.master
(
  id int4 PRIMARY KEY,
  master_name varchar(30)
) WITHOUT OIDS;

CREATE TABLE public.detail
(
  id int4 PRIMARY KEY,
  code_type char(1) REFERENCES public.type,
  id_master int4 REFERENCES public.master,
  detail_name varchar(30)
) WITHOUT OIDS; 
 

--  DATA

INSERT INTO public.type VALUES('A', 'TA');
INSERT INTO public.type VALUES('B', 'TB');
INSERT INTO public.type VALUES('C', 'TC'); 
 
INSERT INTO public.master VALUES(1, 'M1');
INSERT INTO public.master VALUES(2, 'M2');
INSERT INTO public.master VALUES(3, 'M3'); 
 
INSERT INTO public.detail VALUES(1,  'A', 1, 'M1, D1');
INSERT INTO public.detail VALUES(2,  'B', 1, 'M1, D2');
INSERT INTO public.detail VALUES(3,  'A', 1, 'M1, D3');
INSERT INTO public.detail VALUES(4,  'C', 1, 'M1, D4');
INSERT INTO public.detail VALUES(5,  'C', 2, 'M2, D1');
INSERT INTO public.detail VALUES(6,  'A', 3, 'M3, D1');
INSERT INTO public.detail VALUES(7,  'A', 3, 'M3, D2');
INSERT INTO public.detail VALUES(8,  'B', 3, 'M3, D3');
INSERT INTO public.detail VALUES(9,  'A', 3, 'M3, D4');
INSERT INTO public.detail VALUES(10, 'B', 3, 'M3, D5');
INSERT INTO public.detail VALUES(11, 'C', 3, 'M3, D6');
INSERT INTO public.detail VALUES(12, 'C', 3, 'M3, D7'); 
 

--  QUERY

SELECT
  master_name,
  detail_name,
  type 
 
FROM
  master INNER JOIN detail
  ON master.id = detail.id_master 
 
  INNER JOIN type
  ON detail.code_type = type.code 
 
ORDER by master.id, detail.id; 

 


The result of that is:

--
master_name | detail_name | type |
--
M1  | M1, D1  | TA   |
M1  | M1, D2  | TB   |
M1  | M1, D3  | TA   |
M1  | M1, D4  | TC   |
M2  | M2, D1  | TC   |
M3  | M3, D1  | TA   |
M3  | M3, D2  | TA   |
M3  | M3, D3  | TB   |
M3  | M3, D4  | TA   |
M3  | M3, D5  | TB   |
M3  | M3, D6  | TC   |
M3  | M3, D7  | TC   |
--


I need something like this:


master_name | TA | TB | TC |

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



Does anyone know how to do that in Postgresql? I run version 7.3.4.

Thanks for any idea you might have.

Philippe Lang



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] sub-select parameter problem

2004-06-10 Thread Philippe Lang 
Hello,

Imagine the following query:

---
SELECT

  tableA.field1,
  tableA.field2,
  
  tableB.field1,
  tableB.field2,

  (
SELECT tableC.field2
FROM tableC
WHERE tableC.field1 = tableB.field1 - 1;
  ) AS p

FROM tableA
INNER JOIN tableB
ON tableA.pk = tableB.FK;
---

It works fine.

Now, I need to do something else: the parameter of my sub-select is also
a member of the table I'm selecting.

---
SELECT

  tableA.field1,
  tableA.field2,
  
  tableB.field1,
  tableB.field2,

  (
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = tableB.field1 (--> from-main-select?) - 1;
  ) AS p

FROM tableA
INNER JOIN tableB
ON tableA.pk = tableB.FK;
---

How can I refer to the tableB.field1 parameter from the main query? I've
tried to do something like this, but without success:

---
SELECT

  tableA.field1,
  tableA.field2,
  
  tableB.field1 AS param,
  tableB.field2,

  (
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = param - 1;(--> does not work...)
  ) AS p

FROM tableA
INNER JOIN tableB
ON tableA.pk = tableB.FK;
---

The only workaround I found is to use CASE... WHEN, but this is not
really robust, nor elegant.

---
SELECT

  tableA.field1,
  tableA.field2,
  
  tableB.field1,
  tableB.field2,

  CASE

  WHEN tableB.field1 = 1 THEN
  (
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = 0;
  )

  WHEN tableB.field1 = 2 THEN
  (
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = 1;
  )

  WHEN tableB.field1 = 3 THEN
  (
SELECT tableB.field2
FROM tableB
WHERE tableB.field1 = 2;
  )

  ... etc...

  ELSE
  0

  END AS p,

FROM tableA
INNER JOIN tableB
ON tableA.pk = tableB.FK;
---

In my particular application, this is almost acceptable, but I'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 an index scan if your
  joining column's datatypes do not match

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

2004-08-10 Thread Philippe Lang 
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  A  B
2 two  2  A  B
3 three1  A  B

But the same query with a parameter returns an error:

select id, usr, code, line1, line2 from tbl, get_lines(code); 
--> ERROR:  function expression in FROM may not refer to other relations
of same query level

Is there another way to run this query and get:

idusr  code   line1  line2
--
1 one  1  A  B
2 two  2  Z  Z
3 three1  A  B


Thanks



TEST CODE


CREATE TYPE public.lines AS
(
  line1 varchar(10),
  line2 varchar(10)
); 
 
CREATE TABLE public.tbl
(
  id int4 PRIMARY KEY,
  usr varchar(10), 
  code int4
) WITHOUT OIDS;
 
CREATE FUNCTION public.get_lines(int4)
  RETURNS lines AS
'
DECLARE

   code ALIAS FOR $1; 

   lines  lines%rowtype;

BEGIN
 
   IF code = 1 THEN
  lines.line1 = ''A''; 
  lines.line2 = ''B''; 
   ELSE 
  lines.line1 = ''Z''; 
  lines.line2 = ''Z''; 
   END IF; 

   RETURN lines;

END;
'
  LANGUAGE 'plpgsql' VOLATILE; 
 
INSERT INTO tbl VALUES (1, 'one', 1); 
INSERT INTO tbl VALUES (2, 'two', 2); 
INSERT INTO tbl VALUES (3, 'three', 1); 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

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

2004-08-10 Thread Philippe Lang 
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 not sure it's possible to implement it with
a SELECT... CASE. What's more, the "get_lines" function already exists,
and is already used in different parts of the database. I would like, if
possible, to use it without changing it, or duplicating code.

Philippe

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

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

2004-08-11 Thread Philippe Lang 
I found one solution:

select
  id,
  usr,
  code,
  (get_lines(code)).line1,
  (get_lines(code)).line2
from tbl;


The problem now is that get_lines is being called twice per line. (I check with a 
RAISE NOTICE).


Philippe


-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[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 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 not sure it's possible to implement it with a SELECT... CASE. 
What's more, the "get_lines" function already exists, and is already used in different 
parts of the database. I would like, if possible, to use it without changing it, or 
duplicating code.

Philippe

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

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

2004-08-12 Thread Philippe Lang 
> > 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,
  (get_lines(code)).line1,
  (get_lines(code)).line2
from tbl;

I wish there was a way to run the query like this:

select
  id,
  usr,
  code,
  CAST(get_lines(code) as lines)
from tbl;



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

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

2004-08-13 Thread Philippe Lang 
Thanks a lot for your support. With a subselect and offset 0, the function is called 
only once per row, that's fine.

Here is the final test code, in case it can help anyone.

---

CREATE TYPE public.lines AS
(
  line1 varchar(10),
  line2 varchar(10)
);

CREATE TABLE public.tbl
(
  id int4 PRIMARY KEY,
  usr varchar(10),
  code int4
) WITHOUT OIDS;

CREATE FUNCTION public.get_lines(int4)
  RETURNS lines AS
'
DECLARE

   code ALIAS FOR $1;

   lines  lines%rowtype;

BEGIN

   IF code = 1 THEN
  lines.line1 = ''A'';
  lines.line2 = ''B'';
   ELSE
  lines.line1 = ''Z'';
  lines.line2 = ''Z'';
   END IF;

   RAISE NOTICE ''---> get_lines was called...'';

   RETURN lines;

END;
'
  LANGUAGE 'plpgsql' VOLATILE;

INSERT INTO tbl VALUES (1, 'one', 1);
INSERT INTO tbl VALUES (2, 'two', 2);
INSERT INTO tbl VALUES (3, 'three', 1);
INSERT INTO tbl VALUES (4, 'four', 2);

select
   id,
   usr,
   code,
   (get_lines_data).line1,
   (get_lines_data).line2

from
(
   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
Cc : [EMAIL PROTECTED]
Objet : Re: [SQL] function expression in FROM may not refer to other relations of same 
query level 

"Philippe Lang" <[EMAIL PROTECTED]> writes:
> I wish there was a way to run the query like this:

> select
>   id,
>   usr,
>   code,
>   CAST(get_lines(code) as lines)
> from tbl;

You can do something like this:

regression=# create type complex as (r float8, i float8); CREATE TYPE regression=# 
create function fooey(float8) returns complex as regression-# 'select $1 + 1, $1 + 2' 
language sql; CREATE FUNCTION regression=# select f1, (fooey).r, (fooey).i from 
regression-# (select f1, fooey(f1) as fooey from float8_tbl) ss;
  f1   |   r   |   i
---+---+---
 0 | 1 | 2
-34.84 |-33.84 |-32.84
   -1004.3 |   -1003.3 |   -1002.3
 -1.2345678901234e+200 | -1.2345678901234e+200 | -1.2345678901234e+200
 -1.2345678901234e-200 | 1 | 2
(5 rows)

Note the odd-looking parenthesization --- you can't write just "fooey.r"
because that looks like it should be a table and field name, not a field name that is 
selected from.

If the sub-select is too simple, as it is in this case, the planner is likely to 
"flatten out" the query into

select f1, (fooey(f1)).r, (fooey(f1)).i from float8_tbl;

thus defeating your purpose of not calling the function twice.  The currently best 
hack for preventing this is to add "OFFSET 0" to the
sub-select:

select f1, (fooey).r, (fooey).i from
(select f1, fooey(f1) as fooey from float8_tbl offset 0) ss;

regards, tom lane


---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Disable trigger with Postgresql 7.4.x?

2004-08-24 Thread Philippe Lang 
Hello,

I read in an old thread

http://archives.postgresql.org/pgsql-hackers/2002-08/msg00079.php

... that starting with Postgresql 7.3, there is a new way to disable and
enable triggers in Postgresql. The "old" way was:

update pg_class set reltriggers=0 where relname = '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)---
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.

2004-08-24 Thread Philippe Lang 
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
Envoyé : mardi, 24. août 2004 11:26
À : Patrice OLIVER
Cc : [EMAIL PROTECTED]
Objet : Re: [SQL] stored procedures and type of returned result.

Patrice OLIVER wrote:
> Hello,
> 
> I'm new in PostgreSQL. It's very cool.

Hello, and yes it is isn't it :-)

> I would like to know how to return a set of records from a stored 
> procedure.
> 
> For example, i would like to execute these sql command from a stored 
> procedure :
> 
> select t.typnum, t.typcom, t.typcateg, s.symurlgraph from structure.type t
>   left join structure.symbole s
>   on t.typcode = s.typcode;

Broadly speaking you'll want something like:

CREATE TYPE my_return_type AS (
   a  integer,
   b  text
);

CREATE FUNCTION my_function(integer) RETURNS SETOF my_return_type AS '
   SELECT foo_a, foo_b FROM foo WHERE foo_c = $1; ' LANGUAGE SQL;

You don't need to define your own type if you want to return the same columns as a 
table, you can use the table-name instead.

For more complex cases where you need procedural code, you probably want to read 
Stephan Szabo's set-returning-functions article on techdocs.
   http://techdocs.postgresql.org/guides/SetReturningFunctions

HTH
-- 
   Richard Huxton
   Archonet Ltd

---(end of broadcast)---
TIP 3: 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



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Datetime conversion in WHERE clause

2004-09-09 Thread Philippe Lang 
Hello,

I'm converting a varchar to a date in a search routine which looks like:

--
CREATE FUNCTION public.search_data(varchar)
  RETURNS SETOF foo1 AS
'
  SELECT DISTINCT

  foo1.*

  FROM foo1
  LEFT JOIN foo2
  ON foo2.fk = foo1.pk

  WHERE
 lower(foo1.text_data1) LIKE lower($1)
  OR lower(foo1.text_data2) LIKE lower($1)
  OR foo1.date_data = $1::text:date

  OR lower(foo2.text_data3) LIKE lower($1)
  OR lower(foo2.text_data4) LIKE lower($1)
'
  LANGUAGE 'sql' VOLATILE;
--

The problem with the ::text::date conversion is that the whole function
fails if the search string parameter cannot be converted into a valid
date, like 'test%', for example, which is valid for the other text
fields.

Is there a way to force the date conversion to fail sliently, and 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

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

2004-10-13 Thread Philippe Lang 
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 when run inside a plperlu function, lines are being truncated after
a certain width.

Is that a known limitation of plperlu? Or maybe something else?



Philippe


--  TYPE: line_type

CREATE TYPE public.line_type AS
(
  line text
);


--  UTILITY FUNCTION: ps

CREATE FUNCTION public.ps()
  RETURNS text[] AS
'
  $ps = `ps -afux | grep postgres`;
  @fields = split /\n/, $ps;
  return "{" . join(",", @fields) . "}";
'
  LANGUAGE 'plperlu' VOLATILE;


--  UTILITY FUNCTION: show_users

CREATE FUNCTION public.show_users()
  RETURNS SETOF user_type AS
'
DECLARE
  userstext[];
  user_rec line_type%ROWTYPE;
  iint2;

BEGIN
  users = ps();

  FOR i IN 1 .. array_upper(users, 1) LOOP

user_rec.line = users[i];
RETURN NEXT user_rec;

  END LOOP;

  RETURN;
END
'
  LANGUAGE 'plpgsql' VOLATILE;


--  MAIN

select * from show_users();


---(end of broadcast)---
TIP 8: explain analyze is your friend

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

2004-10-13 Thread Philippe Lang 
Hello,

Yes, I made a typo, sorry. I've been playing with different types this afternoon, 
that's why.
But "user_type" was also made up of a "text" field, just like "line_type", so the 
problem is still here...

-Message d'origine-
De : Richard 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 a typo...

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

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

2004-10-13 Thread Philippe Lang 
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 value -> truncated?

On Wed, Oct 13, 2004 at 05:07:05PM +0200, Philippe Lang wrote:

> When run directly from a shell, this code:
> 
>   $ps = `ps -afux | grep postgres`;
>   @fields = split /\n/, $ps;
>   return "{" . join(",", @fields) . "}";
> 
> ... runs fine.
> 
> But when run inside a plperlu function, lines are being truncated 
> after a certain width.

Many versions of ps truncate lines at a certain length if they can't determine the 
terminal size.  Since you're running ps from inside the backend, there's probably no 
terminal to check.  Check the ps manual page to see if it supports an option like -w 
(or multiple occurrences of -w) to increase the line length.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Reuse previously calculated column in sql query?

2004-10-20 Thread Philippe Lang 
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.75 AS col3
FROM foo;


Philippe

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Postgresql inheritance workaround

2005-02-24 Thread Philippe Lang 
Hi,

Because of the actual limitations of Postgresql inheritance mecanism
regarding constraints, I'm not willing to use it in my project, although
it's exactly the kind of idea I need:

In an order and manufacturing management system, several different items
can be ordered, each of them with specific input parameters.

Every item has its own table of "specific parameters", now, what is the
best use of PK/FK in order to maintain integrity between the order lines
and the items specific parameters tables?

Has anyone already done that before?

Thanks for your help, and for 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] DROP TYPE without error?

2005-04-06 Thread Philippe Lang 
Hi,

Since it is not possible to use CREATE OR REPLACE TYPE, is there a way
of using DROP TYPE on a non-existing type, without causing the entire
script to abort? It may sound crazy to ask for this, but it could be
really useful in my case, where Pl/Pgsql and Pl/Perl code is being
generated automatically, based on data found in a database.

Thanks

-
Philippe Lang


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] outer join in ms query

2005-04-18 Thread Philippe Lang 
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
À : [email protected]
Objet : Re: [SQL] outer join in ms query

MS Query is crippled.  If you want to do much from Excel, you will need to 
write code.  But it's not difficult.  ADO is a good choice for this.

"gad renert via DBMonster.com" <[EMAIL PROTECTED]> wrote in message news:[EMAIL 
PROTECTED]
> Hello, I am a novice with SQL, I have 2 tables
>  (transfer)
> bar_code | quant | date
> 723445...|   2   |01/03/2005
>
> (imports)
>
> bar_code | serial_no | supplier
>
>
> I want to add to the first table (transfer) next to each barcode the 
> suppliers name from the (imports) table.
>
> ms query doesn't allow me to do outer joins.
>
> I tried:
> SELECT transfer.bar_code, transfer.quant, imports.supplier FROM 
> transfer left join imports on transfer.bar_code=imports.bar_cod GROUP 
> BY transfer.bar_code
>
> and it didn't work popperly, the (transfer) table is modified.
> what i want to do is very similar to the VLOOKUP function in EXCELL
>
> Hope that someone can help me.
>
> Gadi
>
> --
> Message posted via http://www.dbmonster.com



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[SQL] Concatenate strings using GROUP BY

2006-02-09 Thread Philippe Lang 
Hi,

Let's say we have a query like:

SELECT id, fk, str 
FROM foo

... which returns:

idfkstr
---
1 1 a
2 1 b
3 2 c

Is there a pure SQL way of contactenating str values into a single string,
and get instead:

idfkstr
---
1 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
Switzerland
http://www.attiksystem.ch

Tel:  +41 (26) 422 13 75 
Fax:  +41 (26) 422 13 76
Email:[EMAIL PROTECTED]


smime.p7s
Description: S/MIME cryptographic signature

[SQL] pgxml & xpath_table

2006-06-08 Thread Philippe Lang 
Hi,

I'm playing with the contrib/pgxml library under PG 8.1.4, and I'm not sure if 
what I found with pgxml is a feature of a bug:

I've got the following table:


CREATE TABLE test
(
  id int4 NOT NULL,
  xml varchar(200),
  CONSTRAINT pk PRIMARY KEY (id)
) 
WITHOUT OIDS;

INSERT INTO test VALUES (1, '123112233');

INSERT INTO test VALUES (2, '111222333111222333');



If I launch this query:


select * from

xpath_table('id','xml','test', 
'/doc/@num|/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c','1=1') AS t(id 
int4, doc_num varchar(10), line_num varchar(10), val1 int4, val2 int4, val3 
int4)

where id = 1

order by doc_num, line_num


I get:


iddoc_num line_numval1val2val3
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 System
rte de la Fonderie 2
1700 Fribourg
Switzerland
http://www.attiksystem.ch

Tel:  +41 (26) 422 13 75
Fax:  +41 (26) 422 13 76 


smime.p7s
Description: S/MIME cryptographic signature

[SQL] WITH RECURSION output ordering with trees

2009-07-10 Thread Philippe Lang 
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:

-
--DROP TABLE recursion;

CREATE TABLE recursion
(
  id serial,
  lookup varchar(16),
  parent_id integer,
  primary key(id),
  foreign key(parent_id) references recursion(id)
);

INSERT INTO recursion VALUES(1,'a1', NULL);
INSERT INTO recursion VALUES(2,'b11',1);
INSERT INTO recursion VALUES(645,  'c111',   2);
INSERT INTO recursion VALUES(823,  'c112',   2);
INSERT INTO recursion VALUES(243,  'c113',   2);
INSERT INTO recursion VALUES(6,'b12',1);
INSERT INTO recursion VALUES(845,  'c121',   6);
INSERT INTO recursion VALUES(583,  'c122',   6);
INSERT INTO recursion VALUES(9,'b13',1);
INSERT INTO recursion VALUES(10,   'c131',   9);

WITH RECURSIVE parse_tree (depth, id, lookup, parent_id) AS
(
  SELECT 
0,
parent.id, 
parent.lookup, 
parent.parent_id 
  FROM recursion AS parent 
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT 
parent.depth + 1,
child.id, 
child.lookup, 
child.parent_id 
  FROM parse_tree parent, recursion AS child
  WHERE child.parent_id = parent.id
)

SELECT * FROM parse_tree;
-

Here is the result:

 depth | id  | lookup | parent_id
---+-++---
 0 |   1 | a1 |
 1 |   2 | b11| 1
 1 |   6 | b12| 1
 1 |   9 | b13| 1
 2 | 645 | c111   | 2
 2 | 823 | c112   | 2
 2 | 243 | c113   | 2
 2 | 845 | c121   | 6
 2 | 583 | c122   | 6
 2 |  10 | c131   | 9

I'd like to perform a real recursion, and show the tree structure in a
more appopriate way, like this:

 depth | id  | lookup | parent_id
---+-++---
 0 |   1 | a1 |
 1 |   2 | b11| 1
 2 | 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

Any idea 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] WITH RECURSION output ordering with trees

2009-07-10 Thread Philippe Lang 
[email protected] wrote:
> 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:
> 
> -
> --DROP TABLE recursion;
> 
> CREATE TABLE recursion
> (
>   id serial,
>   lookup varchar(16),
>   parent_id integer,
>   primary key(id),
>   foreign key(parent_id) references recursion(id) );
> 
> INSERT INTO recursion VALUES(1,'a1', NULL);
> INSERT INTO recursion VALUES(2,'b11',1);
> INSERT INTO recursion VALUES(645,  'c111',   2);
> INSERT INTO recursion VALUES(823,  'c112',   2);
> INSERT INTO recursion VALUES(243,  'c113',   2);
> INSERT INTO recursion VALUES(6,'b12',1);
> INSERT INTO recursion VALUES(845,  'c121',   6);
> INSERT INTO recursion VALUES(583,  'c122',   6);
> INSERT INTO recursion VALUES(9,'b13',1);
> INSERT INTO recursion VALUES(10,   'c131',   9);
> 
> WITH RECURSIVE parse_tree (depth, id, lookup, parent_id) AS (
>   SELECT
> 0,
> parent.id,
> parent.lookup,
> parent.parent_id
>   FROM recursion AS parent
>   WHERE parent_id IS NULL
> 
>   UNION ALL
> 
>   SELECT
> parent.depth + 1,
> child.id,
> child.lookup,
> child.parent_id
>   FROM parse_tree parent, recursion AS child
>   WHERE child.parent_id = parent.id
> )
> 
> SELECT * FROM parse_tree;
> -
> 
> Here is the result:
> 
>  depth | id  | lookup | parent_id
> ---+-++---
>  0 |   1 | a1 |
>  1 |   2 | b11| 1
>  1 |   6 | b12| 1
>  1 |   9 | b13| 1
>  2 | 645 | c111   | 2
>  2 | 823 | c112   | 2
>  2 | 243 | c113   | 2
>  2 | 845 | c121   | 6
>  2 | 583 | c122   | 6
>  2 |  10 | c131   | 9
> 
> I'd like to perform a real recursion, and show the tree structure in
> a more appopriate way, like this: 
> 
>  depth | id  | lookup | parent_id
> ---+-++---
>  0 |   1 | a1 |
>  1 |   2 | b11| 1
>  2 | 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
> 
> Any idea how to do that? (without trying to sort on the lookup
> column, whose values can be random outside this test) 

Hi again,

I reply to my own post: I found a way to parse the tree with the help of
the tablefunc contrib package:

-
SELECT

t.depth,
t.id,
r.lookup,
t.parent_id

FROM connectby('recursion', 'id', 'parent_id', 'lookup', '1', 0)
AS t(id integer, parent_id integer, depth integer, o integer)

INNER JOIN recursion AS r
ON t.id = r.id
-

 depth | id  | lookup | parent_id
---+-++---
 0 |   1 | a1 |
 1 |   2 | b11| 1
 2 | 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] WITH RECURSION output ordering with trees

2009-07-10 Thread Philippe Lang 
[email protected] 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:
>> 
>> I'd like to perform a real recursion, and show the tree structure in
>> a more appopriate way, like this:
>> 
>> Any idea how to do that? (without trying to sort on the lookup
>> column, whose values can be random outside this test)
> 
> 
> The manual has a nice hint on this adding up IDs to "generate" a path
> like column that can be used for sorting. 
> 
> Try the following:
> 
> WITH RECURSIVE parse_tree (depth, id, lookup, parent_id, sort_path)
>   AS ( SELECT 0,
> parent.id,
> cast(parent.lookup as text),
> parent.parent_id,
> array[0] as sort_path
>   FROM recursion_sample parent
>   WHERE parent_id IS NULL
>   UNION ALL
>   SELECT
> parent.depth + 1,
> child.id,
> rpad(' ', depth * 2) || child.lookup,
> child.parent_id,
> parent.sort_path || child.id
>   FROM parse_tree parent JOIN recursion_sample child on
> child.parent_id = parent.id )
> select id, lookup
> from parse_tree
> order by sort_path
> ;
> 
> This will output:
> 
>  id  | lookup
> -+
>1 | a1
>2 | b11
>  243 |   c113
>  645 |   c111
>  823 |   c112
>6 | b12
>  583 |   c122
>  845 |   c121
>9 | b13
>   10 |   c131
> (10 rows)

Hi Thomas,

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 has no meaning at all?

If yes, we would get instead:

 depth | id  | lookup | parent_id
---+-++---
 0 |   1 | a1 |
 1 |   2 | b11| 1
 2 | 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

Best regards,

Philippe Lang

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] WITH RECURSION output ordering with trees

2009-07-10 Thread Philippe Lang 
[email protected] 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 has no meaning at all?
> 
>> If yes, we would get instead:
> 
>>  depth | id  | lookup | parent_id
>> ---+-++---
>>  0 |   1 | a1 |
>>  1 |   2 | b11| 1
>>  2 | 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
> 
> Try this:
> 
> WITH RECURSIVE parse_tree (depth, id, lookup, parent_id, path) AS (
>   SELECT 0, parent.id, parent.lookup, parent.parent_id,
>   parent.lookup::text FROM recursion AS parent
>   WHERE parent_id IS NULL
> UNION ALL
>   SELECT parent.depth + 1, child.id, child.lookup, child.parent_id,
>  parent.path || '.' || child.lookup
>   FROM parse_tree parent
>   JOIN recursion AS child ON child.parent_id = parent.id
> )
> SELECT depth, id, lookup, parent_id
> FROM parse_tree
> ORDER BY path

Works great, thanks! Of course, concatenating lookups...

Best regards,

Philippe

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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

2006-09-05 Thread Philippe Lang 
[EMAIL PROTECTED] wrote:

> for Reverse DNS purposes, I'd like to reverse an IP ...
> 
> ie: 200.46.204.1 would become 1.204.46.200
> 
> Is there an easy way of doing this *short* of writing a
> plpgsql function?
> 
> I've checked the docs, and found the substring() function
> that allows for using a regex, which I thought might allow
> this, but can't seem to figure out a proper format for it ;(
> 
> If I have to write a function to do it, fine ... just wanted
> to make sure I wasn't missing something first ...


Hi,

You can use (\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) 422 13 76  


smime.p7s
Description: S/MIME cryptographic signature

[SQL] Table transform query

2007-09-18 Thread Philippe Lang 
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 1   L


... into this:


serial  dateL  dateR

1   1  2
1   4   
2   1  2
3   1  3
4   2  3
5   3   


Basically, data on table 1 means: 

- on date 1, product with serial 1 is sent to the customer
- on date 1, product with serial 2 is sent to the customer
- on date 1, product with serial 3 is sent to the customer
- on date 2, product with serial 1 comes back
...

On table 2, data means:

- Product with serial 1 is sent to the customer on date 1, and comes
back on date 2
- Product with serial 1 is sent to the customer on date 4, and hasn't
come back yet
...


Do you think there is a generic SQL solution to this problem, like
crosstab or pivot table?

I'm thinking of doing things in a "procedural" plpgsql manner...

Any idea is welcome.

Thanks!

Philippe

---(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] Table transform query

2007-09-18 Thread Philippe Lang 
[EMAIL PROTECTED] wrote:

> Hi,
> 
> I'm trying to find out how to transform this kind of table data
> (history of rental data in a firm): 

... 

I have answred my own question: yes, there is a pure SQL solution, with
a subselect:

CREATE TABLE foo (
serial integer,
delivery character(1),
date integer
);

INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 1);
INSERT INTO foo (serial, delivery, date) VALUES (2, 'L', 1);
INSERT INTO foo (serial, delivery, date) VALUES (3, 'L', 1);
INSERT INTO foo (serial, delivery, date) VALUES (1, 'R', 2);
INSERT INTO foo (serial, delivery, date) VALUES (2, 'R', 2);
INSERT INTO foo (serial, delivery, date) VALUES (4, 'L', 2);
INSERT INTO foo (serial, delivery, date) VALUES (5, 'L', 3);
INSERT INTO foo (serial, delivery, date) VALUES (3, 'R', 3);
INSERT INTO foo (serial, delivery, date) VALUES (4, 'R', 3);
INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 4);


-
select 

f.serial, 
f.date as dateL,
( select f2.date from foo as f2 
  where f2.serial = f.serial 
  and f2.date > f.date 
  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

Re: [SQL] Table transform query

2007-09-21 Thread Philippe Lang 

> 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 the correlated
> subquery (which could be  simplified by using MIN instead of LIMIT 1)
> is up for debate and test, respectively.  

Hi Nis,

Thanks for your tip with the "MIN" operator.


I always imagined a self-join solution was faster than a query with a 
subselect. With a quick test, it seems to be the case here.



CREATE TABLE foo (
serial integer,
delivery character(1),
date integer
);

INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 1); INSERT INTO foo 
(serial, delivery, date) VALUES (2, 'L', 1); INSERT INTO foo (serial, delivery, 
date) VALUES (3, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (1, 
'R', 2); INSERT INTO foo (serial, delivery, date) VALUES (2, 'R', 2); INSERT 
INTO foo (serial, delivery, date) VALUES (4, 'L', 2); INSERT INTO foo (serial, 
delivery, date) VALUES (5, 'L', 3); INSERT INTO foo (serial, delivery, date) 
VALUES (3, 'R', 3); INSERT INTO foo (serial, delivery, date) VALUES (4, 'R', 
3); INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 4);


-- Subselect
SELECT 
f.serial,
f.date as dateL,
( SELECT MIN(f2.date)
  FROM foo AS f2
  WHERE f2.serial = f.serial
  AND f2.date > f.date
  AND f2.delivery = 'R' 
) AS dateR
FROM foo AS f
WHERE f.delivery = 'L'
ORDER BY f.serial, f.date

-- Self-join
SELECT 
t1.serial, 
t1.date as dateL, 
MIN(t2.date) as dateR 
FROM foo t1 
LEFT JOIN foo 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
ORDER BY t1.serial, t1.date

---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] Temporal databases

2007-11-07 Thread Philippe Lang 
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 your joining column's datatypes do not
   match

[SQL] quote_nullable alternative?

2007-11-08 Thread Philippe Lang 
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 alternative to patching the server in order to get that kind
of feature, for example with plpgsql code?

Because of that bug:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.19

... I'm obliged to call EXECUTE at every temporary table call, and
constructing the query string where parameters can be NULL is really a
nightmare...

Thanks

Philippe Lang

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] quote_nullable alternative?

2007-11-08 Thread Philippe Lang 
[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 alternative to patching the server in order to get that
> kind of feature, for example with plpgsql code? 
> 
> Because of that bug:
> 
> http://www.postgresql.org/docs/faqs.FAQ.html#item4.19
> 
> ... I'm obliged to call EXECUTE at every temporary table call, and
> constructing the query string where parameters can be NULL is really
> a nightmare...  
> 
> Thanks

Hi,

It might not be the most elegant solution, but I ended up writing these
functions to solve my problem:


CREATE OR REPLACE FUNCTION nullable_param(param text)
  RETURNS text AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN  || param || ;
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;

  CREATE OR REPLACE FUNCTION nullable_param(param varchar)
  RETURNS varchar AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN  || param || ;
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION nullable_param(param decimal)
  RETURNS varchar AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN CAST(param AS varchar);
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION nullable_param(param int)
  RETURNS varchar AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN CAST(param AS varchar);
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION nullable_param(param date)
  RETURNS varchar AS
$$
  BEGIN
IF param IS NULL THEN
  RETURN 'NULL';
ELSE
  RETURN  || param || ;
END IF;
  END
$$
  LANGUAGE 'plpgsql' VOLATILE;



Regards,

Philippe

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Temporal databases

2007-11-12 Thread Philippe Lang 
[EMAIL PROTECTED] wrote:
> Hi Philippe,
> Have a look at the post I made to the pgsql-sql list I made on 6th
> November 2007 (day before you posted this). It contains alot of stuff
> for temporal tables within Postgres. 
> However temporal tables (when done properly) are a very complex
> subject at the best of times. It depends how much you want to
> integrate them. A good place to start is Joe Celko's book "SQL For
> Smarties" and Richard Snodgrass' book "Developing Time-Oriented
> Database Applications in SQL" - this book is no longer in print, but
> is downloadable (for FREE!) from:
> http://www.cs.arizona.edu/~rts/tdbbook.pdf
> Richard Snodgrass is one of the leading experts in the field. I warn
> you - the book is heavy going - but so worth it!! 
> 
> Keith

Hi Keith,

I saw your post a few minutes after I posted mine, but I wasn't aware of
the free book of Richard Snodgrass. Thanks for the link!

Have you ever heard about some sort of database extension, or plugin,
that allows using databases "temporally" without have to explicitely add
code in into the schema?

Is there an SQL 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 planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Temporal databases

2007-11-14 Thread Philippe Lang 
Keith Carr wrote:
> On Monday 12 November 2007 09:07, you wrote:
> 
> Hi Philippe,
> I do not know of any "extension" or "plugin" that can be used to give
> database engines "temporality". Temporality will always be a tricky
> subject and it would be impossible to code something general that
> would work for any single situation. For example, on some tables you
> may only want Valid Time recorded, on some tables only Transaction
> Time and in some cases both (or in other cases neither). How would it
> know which one? Also, when it came to queries updatating, deleting or
> even just selecting, which criteria would it now to do this by in
> this situation?
> 
> There is no SQL standard for querying temporal databases as far as I
> am aware. 
> This would be considered to be "schema" and so i presume, outside the
> remit of the SQL standards committee. The closest that the SQL
> standards committee will get to this is defining the structure within
> which dates and times will be held and operated on in a "logical"
> sense.
> 
> Sure this makes temporal databases hard work, but this is the whole
> point of a SQL database and SQL programmers - data integrity for a
> given situation!  
> Otherwise we may as well be letting the company's accountants go off
> designing databases using Access and spreadsheets?! And we ALL
> know we don't want that, because when it goes wrong (because there
> was no data   
> integrity) you will be the one left to sort the mess out.. ;)
> 
> Hope this has helped in some way.
> Keith

Hi Keith,

Thanks for your answer.

I haven't been playing with temporal databases at all, so pardon my lack
of precision, but naively I was imaginating something that would more or
less look like:

---
CREATE TABLE foo
(
  id integer,
  s varchar(64)
) WITH TEMPORAL VALID TIME;

SET CURRENT_TIME = '2007-06-01'::date;

INSERT INTO foo(v, s) VALUES (1, 'first line');
INSERT INTO foo(v, s) VALUES (2, 'second line');
INSERT INTO foo(v, s) VALUES (3, 'third line');

SET CURRENT_TIME = '2007-06-02'::date;

INSERT INTO foo(v, s) VALUES (4, 'fourth line');
DELETE FROM foo WHERE v = 1;
---


Now "SET CURRENT_TIME = '2007-06-01'::date; SELECT * from foo;" would
return:

--
id   s
--
1first line
2second line
3third line
--

And "SET CURRENT_TIME = '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

[SQL] Concatenation through SQL

2007-12-21 Thread Philippe Lang 
Hi,

Here is small reduced test database:

--
CREATE TABLE appointments
(
  id integer,
  name varchar(32),
  CONSTRAINT appointments_pkey PRIMARY KEY (id)
);

CREATE TABLE employees
(
  id integer,
  appointment_id integer,
  name varchar(32),
  CONSTRAINT employees_pkey PRIMARY KEY (id),
  CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id)
REFERENCES appointments (id)
);

INSERT INTO appointments VALUES (1, 'app1');
INSERT INTO employees VALUES (1, 1, 'emp1');
INSERT INTO employees VALUES (2, 1, 'emp2');
INSERT INTO employees VALUES (3, 1, 'emp3');

INSERT INTO appointments VALUES (2, 'app2');
INSERT INTO employees VALUES (4, 2, 'emp1');
INSERT INTO employees VALUES (5, 2, 'emp4');
--


I'm trying to write an SQL query that would return this:

---
appointmentcount_employeesemployees
---
app1   3  emp1, emp2, emp3
app2   2  emp1, emp4,
---

First part is easy to write:

--
SELECT 
appointments.name AS appointment,
(SELECT COUNT(*) FROM employees AS e where e.appointment_id =
appointments.id) AS num_employees
FROM appointments
--

... But concatenating employees name is harder, at least for me...

I'm convinced this can be done with Set Returning Functions and a bit of
plpgsql, but I was wondering if it would possible to write some kind of
extension to Postgresql (operator?) that would allow this kind of
syntax:

--
SELECT 
appointments.name AS appointment,
(SELECT COUNT(*) FROM employees AS e where e.appointment_id =
appointments.id) AS num_employees
(SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id =
appointments.id) AS employees
FROM appointments
--

... where CONCAT suggest 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] Concatenation through SQL

2007-12-21 Thread Philippe Lang 
[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=*# select array_to_string(array(select val from a), ', '); 
> array_to_string -
>  foo, bar
> (1 row)
> 
> 
> * comma-aggregate, see
> http://www.zigo.dhs.org/postgresql/#comma_aggregate

Hi,

Thanks to all who responded, in particular Andreas. I'm always amazed by
the quality of both the Postgresql database and the support in its
newsgroups.

The "comma-aggregate" worked just fine for me. Here is the final
example, for those willing to test it. This will be more than useful!

---
CREATE TABLE appointments
(
  id integer,
  name varchar(32),
  CONSTRAINT appointments_pkey PRIMARY KEY (id)
);

CREATE TABLE employees
(
  id integer,
  appointment_id integer,
  name varchar(32),
  CONSTRAINT employees_pkey PRIMARY KEY (id),
  CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id)
REFERENCES appointments (id)
);

INSERT INTO appointments VALUES (1, 'app1');
INSERT INTO employees VALUES (1, 1, 'emp1');
INSERT INTO employees VALUES (2, 1, 'emp2');
INSERT INTO employees VALUES (3, 1, 'emp3');

INSERT INTO appointments VALUES (2, 'app2');
INSERT INTO employees VALUES (4, 2, 'emp1');
INSERT INTO employees VALUES (5, 2, 'emp4');

CREATE AGGREGATE array_accum (anyelement) 
( 
  sfunc = array_append, 
  stype = anyarray, 
  initcond = '{}' 
); 

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 employees
FROM appointments
---


Result:

---
appointmentcount_employeesemployees
---
app1   3  emp1, emp2, emp3
app2   2  emp1, emp4,
---


Merry christmas to all.

Philippe


---(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] Concatenation through SQL

2007-12-21 Thread Philippe Lang 
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 employees FROM
>> appointments ---
>> 
> 
> hello,
> 
> one note: you can merge your two subselect into one. It's unimportant
> on 100 rows, but it's can be important on thousands rows. 
> 
> nice a day
> Pavel

Hi Pavel,

Since subselects must return one single column, is that what you are
talking about? I have just modified the code you have posted before.

---
SELECT 
  name AS appointment, 
  num_employees, 
  employees 

FROM 
(
  SELECT 
appointment_id, 
COUNT(*) AS num_employees, 
(SELECT array_to_string(array_accum(name),', ') FROM employees WHERE
e.appointment_id = appointment_id) AS employees
  FROM employees e 
  GROUP BY appointment_id
)s

JOIN appointments a on a.id = s.appointment_id;
---

Philippe

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[SQL] SQL tree duplication

2008-01-24 Thread Philippe Lang 
Hi,

I have the following kind of sql structure, in 3 levels:

---
create table documents (
  id serial,
  name varchar(50),
  primary key (id)
);

create table lines (
  id serial,
  name varchar(50),
  document_id integer,
  primary key (id),
  foreign key (document_id) references documents (id)
);

create table line_details (
  id serial,
  name varchar(50),
  line_id integer,
  primary key (id),
  foreign key (line_id) references lines (id)
);
---

I'd like to be able to "duplicate" a document, with all of its lines and
line details.

Is there any easy way to do that with Postgresql? The only solution I
can think of at the moment is to loop through all lines and line
details, and replace foreign keys properly with values fetch with
"currval". It should work just fine, but I was wondering if some
advanced features of 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] "Subquery must return only one column" & query optimization

2008-12-15 Thread Philippe Lang 
[email protected] wrote:
> "Philippe Lang"  writes:
>> I was trying to run this query this morning:
> 
>> --
>> SELECT
> 
>> r.*,
> 
>> (
>>  SELECT
> 
>>  rl.reminder_header,
>>  rl.reminder_footer
> 
>>  FROM reminder_levels AS rl
>>  WHERE rl.lookup =
>>  (
>>  SELECT MAX(reminder_level_lookup)
>>  FROM reminders
>>  WHERE customer_id = r.customer_id
>>  )
>> )
> 
>> FROM reminders AS r
>> --
> 
>> Postgresql replied that:
> 
>> --
>> ERROR: subquery must return only one column
> 
> Since 8.0 or so you could write the sub-select as
> 
>   SELECT ROW(rl.reminder_header, rl.reminder_footer) FROM ...
> 
> We ought to make that happen automatically, but it's not real high on
> the to-do list. 

Hi Tom,

Fine, the query is faster now:


SELECT 

r.*,

(
SELECT 

ROW(rl.reminder_header, rl.reminder_header)

FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup) 
FROM reminders
WHERE customer_id = r.customer_id
)
) AS rec

FROM reminders AS r


... but the last column is now of type "record", and since this query
serves as a datasource for a MS Access report, it is not able to "split"
the record into the values that interest me: reminder_header and
reminder_footer!

Regards,

Philippe Lang

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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

2008-12-15 Thread Philippe Lang 
Hi,

I was trying to run this query this morning:

--
SELECT 

r.*,

(
SELECT 

rl.reminder_header,
rl.reminder_footer

FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup) 
FROM reminders
WHERE customer_id = r.customer_id
)
)

FROM reminders AS r
--

Postgresql replied that:

--
ERROR: subquery must return only one column
SQL state: 42601
--

Is there a way to avoid writing:

--
SELECT 

r.*,

(
SELECT 

rl.reminder_header

FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup) 
FROM reminders
WHERE customer_id = r.customer_id
)
) AS reminder_header,

(
SELECT 

rl.reminder_footer

FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup) 
FROM reminders
WHERE customer_id = r.customer_id
)
) AS reminder_footer

FROM reminders AS r
--

... which works, but runs twice the same subselect block:

--
FROM reminder_levels AS rl
WHERE rl.lookup =
(
SELECT MAX(reminder_level_lookup) 
FROM reminders
WHERE customer_id = r.customer_id
)
--

Thanks,

Philippe Lang

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql