Re: [SQL] create default
Sorry, there is a "bug" in my statement (according to debug issues), the
correct statement should be:
CREATE FUNCTION test() RETURNS opaque AS
'BEGIN
IF new.cno1 IS NULL THEN
IF new.cno2 IS NULL THEN
new.cno1 := trim(new.cno3);
ELSIF new.cno3 IS NULL THEN
new.cno1 := trim(new.cno2);
ELSE
new.cno1 := trim(new.cno2)||trim(new.cno3);
END IF;
END IF;
RETURN new;
END;'
LANGUAGE 'plpgsql';
BTW, for getting correct results, you should define cno1 as long as the
maximum length of cno2 and cno3 together (20).
Regards, Jens
Jens Hartwig schrieb:
>
> > hi,how to set default filed+field
> >
> > create table "table1"(
> > "cno1" char(10) default NEW.cno2+NEW.cno3,
> > "cno2" char(10) ,
> > "cno3" char(10)
> > );
> >
> > I try pl/pgsql and pl/tcl NOT RUN,
> >
> > thanks
>
> Try the following:
>
> CREATE FUNCTION test() RETURNS opaque AS
> 'BEGIN
> IF new.cno1 IS NULL THEN
> IF new.cno2 IS NULL THEN
> new.cno1 := substr(new.cno3,1,3);
> ELSIF new.cno3 IS NULL THEN
> new.cno1 := substr(new.cno2,1,3);
> ELSE
> new.cno1 := substr(new.cno2,1,3)||substr(new.cno3,1,3);
> END IF;
> END IF;
> RETURN new;
> END;'
> LANGUAGE 'plpgsql';
>
> CREATE TRIGGER test_trg
> BEFORE INSERT OR UPDATE ON table1
> FOR EACH ROW
> EXECUTE PROCEDURE test();
>
> INSERT INTO table1 (cno2, cno3) values ('abc', 'def');
>
> SELECT * FROM table1;
>
> Best regards, Jens Hartwig
=
Jens Hartwig
-
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax : +49 (0)30 2554-3187
Mobil: +49 (0)170 167-2648
E-Mail : [EMAIL PROTECTED]
=
Re: [SQL] Strange Execution-Plan for NOT EXISTS
Jens Hartwig <[EMAIL PROTECTED]> writes: > select (min(id) + 1) > from t_dummy d1 > where not exists ( > select id > from t_dummy d2 > where d2.id = (d1.id + 1) > ); > Aggregate (cost=2924207.88..2924207.88 rows=1 width=12) -> Seq Scan on t_dummy d1 (cost=0.00..2924207.88 rows=1 width=12) > SubPlan > -> Seq Scan on t_dummy d2 (cost=0.00..331.36 rows=1 width=12) > Why that? Wouldn´t it be possible to simple use the primary key index in > the sub-query I think that 7.0.* is too stupid to consider an indexscan for a qualifier that looks like "indexvar = ($1 + 1)", which is what you effectively have in this case ($1 being the Param passed in from the outer plan). It only recognizes "indexvar = constant" and "indexvar = param" as indexable expressions. Current sources do better. regards, tom lane
Re: Sv: [SQL] how to build this query ??? Please help !!!
I was playing around with subselects and managed to crash the backend (if you try, it can be done I guess): create table x (a numeric); create table y (b numeric); insert into x values (1); insert into x values (2); insert into x values (3); insert into x values (4); insert into x values (5); insert into x values (6); insert into x values (7); insert into x values (8); insert into x values (9); insert into x values (10); insert into x values (11); insert into y values (1); insert into y values (2); insert into y values (3); insert into y values (4); insert into y values (5); insert into y values (6); insert into y values (7); insert into y values (8); insert into y values (9); select a, (select b from y) from x; select a, (select b from y where b = a) from x; -- this is ok ... select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b FROM x WHERE a IN (SELECT b FROM y) GROUP BY a HAVING a IN (SELECT b FROM y WHERE b > 1); a |b --+-- 2.00 | 3.00 3.00 | 4.00 4.00 | 5.00 5.00 | 6.00 6.00 | 7.00 7.00 | 8.00 8.00 | 9.00 9.00 | (8 rows) -- this crashes select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b FROM x WHERE a IN (SELECT b FROM y) GROUP BY a,b HAVING a IN (SELECT b FROM y WHERE b > 1); pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !# \connect subselects No Postgres username specified in startup packet. !> \connect subselects pgcvs You are now connected to database subselects as user pgcvs. subselects=# This example is probably ridiculous and might break syntax!? Else maybe a bug in not liking "AS b". On Thursday 04 January 2001 06:13, Jens Hartwig wrote: > Hello Tom, > > > [...] > > > > > SELECT a, (SELECT b) > > > FROM xyz; > > > > [...] > > I think it's OK (we're assuming that a and b are columns of xyz, right?) > > [...] > > immediately after having sent my message I realized my fault: a and b > are not of the same table! Correctly, the statement had to be something > like: > > SELECT a, (SELECT b FROM z WHERE b = a) > FROM x; > > > [...] > > This is not really different from > >SELECT x FROM xyz WHERE y IN > >(SELECT a FROM abc WHERE b = xyz.z); > > [...] > > Now it is :-) In a subquery, the inner query is only used for things > like comparison (as it is in your example). In my example the result > shows me two columns (in one record!!) which belong to different tables. > Mmmmh ... I tested the following: > > create table x (a numeric); > create table y (b numeric); > > insert into x values (1); > insert into x values (2); > > insert into y values (1); > insert into y values (2); > > select a, (select b from y) from x; > > => ERROR: More than one tuple returned by a subselect used as an > expression. > > This is ok, anything else would have shocked me. > > select a, (select b from y where b = a) from x; > > a | ?column? > --+-- >1.00 | 1.00 >2.00 | 2.00 > > This result made me understanding that this special case of "subqueries" > is possibly nothing more than a special form of joins between tables: > > select a, b > from x, y > where x.a = y.b; > > brings the same result. Now, back to the first example (of Nikolaj): > > SELECT a_nr, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = > 'zdr') AS #zdr, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = > 'zcu') AS #zcu, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = > 'zcr') AS #zcr, > product, state > FROM orders; > > This would be a self-join of one table like: > > select ord.a_nr, > c1.count(*), > c2.count(*), > ... > from orders ord, > cylinders c1, > cylinders c2, > ... > where c1.z_a_nr = ord.a_nr > and c2.z_status = 'zdr' > and ... > > This in fact is not possible in PostgreSQL (it seems that the table > alias "c1" cannot be prefixed to the aggregate-function "count(*)") and > AFAIK in no other relational database. I really cannot imagine any > equivalent join-statement (or anything else like a subquery) which > brings the same results! Does this at all correlate with the philosophy > of a relational database? > > Best regards, Jens > > = > Jens Hartwig > - > debis Systemhaus GEI mbH > 10875 Berlin > Tel. : +49 (0)30 2554-3282 > Fax : +49 (0)30 2554-3187 > Mobil: +49 (0)170 167-2648 > E-Mail : [EMAIL PROTECTED] > ==
Re: Sv: [SQL] how to build this query ??? Please help !!!
Syntax Rules 1) Each in the shall unambiguously reference a column of the table resulting from the . A column referenced in a is a grouping column. NOTE 101 - "Column reference" is defined in Subclause 6.6, "". 1) Every has a qualifying table and a qualifying scope, as defined in succeeding Syntax Rules. The query I did was bad syntax I guess. That b only exists in the result table, not in the table made in the FROM clause. Still, crashes so not good. subselects=# select a, (SELECT b FROM y WHERE y.b = x.a + 1) from x WHERE a IN (SELECT b FROM y) GROUP BY a, b HAVING a IN (SELECT b FROM y WHERE y.b > 1); ERROR: Attribute 'b' not found subselects=# That's what should be expected (right?) and I get it if I remove "AS b". On Monday 08 January 2001 17:24, Robert B. Easter wrote: > I was playing around with subselects and managed to crash the backend (if > you try, it can be done I guess): > > create table x (a numeric); > create table y (b numeric); > > insert into x values (1); > insert into x values (2); > insert into x values (3); > insert into x values (4); > insert into x values (5); > insert into x values (6); > insert into x values (7); > insert into x values (8); > insert into x values (9); > insert into x values (10); > insert into x values (11); > > insert into y values (1); > insert into y values (2); > insert into y values (3); > insert into y values (4); > insert into y values (5); > insert into y values (6); > insert into y values (7); > insert into y values (8); > insert into y values (9); > > select a, (select b from y) from x; > select a, (select b from y where b = a) from x; > > -- this is ok ... > select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b > FROM x WHERE a IN (SELECT b FROM y) > GROUP BY a > HAVING a IN (SELECT b FROM y WHERE b > 1); > a |b > --+-- > 2.00 | 3.00 > 3.00 | 4.00 > 4.00 | 5.00 > 5.00 | 6.00 > 6.00 | 7.00 > 7.00 | 8.00 > 8.00 | 9.00 > 9.00 | > (8 rows) > > -- this crashes > select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b > FROM x WHERE a IN (SELECT b FROM y) > GROUP BY a,b > HAVING a IN (SELECT b FROM y WHERE b > 1); > > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > !# \connect subselects > No Postgres username specified in startup packet. > !> \connect subselects pgcvs > You are now connected to database subselects as user pgcvs. > subselects=# > > This example is probably ridiculous and might break syntax!? Else maybe a > bug in not liking "AS b". > > On Thursday 04 January 2001 06:13, Jens Hartwig wrote: > > Hello Tom, > > > > > [...] > > > > > > > SELECT a, (SELECT b) > > > > FROM xyz; > > > > > > [...] > > > I think it's OK (we're assuming that a and b are columns of xyz, > > > right?) [...] > > > > immediately after having sent my message I realized my fault: a and b > > are not of the same table! Correctly, the statement had to be something > > like: > > > > SELECT a, (SELECT b FROM z WHERE b = a) > > FROM x; > > > > > [...] > > > This is not really different from > > >SELECT x FROM xyz WHERE y IN > > >(SELECT a FROM abc WHERE b = xyz.z); > > > [...] > > > > Now it is :-) In a subquery, the inner query is only used for things > > like comparison (as it is in your example). In my example the result > > shows me two columns (in one record!!) which belong to different tables. > > Mmmmh ... I tested the following: > > > > create table x (a numeric); > > create table y (b numeric); > > > > insert into x values (1); > > insert into x values (2); > > > > insert into y values (1); > > insert into y values (2); > > > > select a, (select b from y) from x; > > > > => ERROR: More than one tuple returned by a subselect used as an > > expression. > > > > This is ok, anything else would have shocked me. > > > > select a, (select b from y where b = a) from x; > > > > a | ?column? > > --+-- > >1.00 | 1.00 > >2.00 | 2.00 > > > > This result made me understanding that this special case of "subqueries" > > is possibly nothing more than a special form of joins between tables: > > > > select a, b > > from x, y > > where x.a = y.b; > > > > brings the same result. Now, back to the first example (of Nikolaj): > > > > SELECT a_nr, > > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, > > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = > > 'zdr') AS #zdr, > > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = > > 'zcu') AS #zcu, > > (SELECT count(*) FROM
Re: Sv: [SQL] how to build this query ??? Please help !!!
"Robert B. Easter" <[EMAIL PROTECTED]> writes: > The query I did was bad syntax I guess. No, it's just a bug. PG accepts GROUP BY items that are references to output columns, cf. http://www.postgresql.org/devel-corner/docs/postgres/sql-select.htm Looks like it gets confused when the grouped-by item contains a subselect, however. regards, tom lane
[SQL] hex number
Hi, Does anybody knows that is any function can covert an inet(IP addr) type to a hex number?? Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: Sv: [SQL] how to build this query ??? Please help !!!
"Robert B. Easter" <[EMAIL PROTECTED]> writes: > -- this crashes > select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b > FROM x WHERE a IN (SELECT b FROM y) > GROUP BY a,b > HAVING a IN (SELECT b FROM y WHERE b > 1); Fixed. 'Twas another case of trying to close a subplan twice ... regards, tom lane
[SQL] subselect test
SELECT a, (SELECT b FROM y LEFT JOIN n ON b = c WHERE b = a + 1) AS b, c FROM (SELECT * FROM y CROSS JOIN (x NATURAL RIGHT JOIN n)) AS z NATURAL JOIN (SELECT * FROM x) AS zz WHERE a NOT IN (SELECT b FROM y NATURAL JOIN x WHERE b > 5) GROUP BY a, b, c HAVING a IN (SELECT b FROM y LEFT JOIN x ON (a = b) CROSS JOIN n WHERE b > 1) INTERSECT SELECT * FROM x,y,n EXCEPT SELECT * FROM x,y,n WHERE a < 3 ORDER BY a DESC LIMIT 10; a |b |c --+--+-- 5.00 | 6.00 | 1.00 5.00 | 6.00 | 2.00 5.00 | 6.00 | 3.00 5.00 | 6.00 | 4.00 5.00 | 6.00 | 5.00 5.00 | 6.00 | 6.00 5.00 | 6.00 | 7.00 4.00 | 5.00 | 1.00 4.00 | 5.00 | 2.00 4.00 | 5.00 | 3.00 (10 rows) Is this confusing? Is the result correct? Anyway, these new complex queries seem to be doing something in 7.1 cvs! -- Robert B. Easter [EMAIL PROTECTED] - -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- -- http://www.comptechnews.com/~reaster/
