[SQL] type regclass casting
hello look select * from pg_class where oid = 'sometablename'::regclass; (1 row) select * from pg_class where oid = 'sometablename'::text::regclass; ERROR can not cast TEXT to regclass What does this mean ? version 7.3.3 please try it on 7.4.x ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 7.4 - FK constraint performance
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Can't see how we optimize your case without pessimizing more-common cases. My case appears to be pretty common, i.e. 1 small and 1 large table with RI constraint between them. In order to delete a record from the small table, the large table must not have records that are dependent on the deleted row. I think other RDBMSs simply use preset value instead of partial table scan when there's not enough stat info. Might be a better way. Thanks __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] joining a table with a function
hi all, i have a function of a user defined type it returns a record of values but just a row. select * from rec_f_consultar_alumno(a.ent_codigo, a.alu_codigo) what i want to do now is a select in wich i can do a seq scan or an index scan of a table and pass the values i retrieve into the function. the select have to retrieve the result of the function. i can do that whit a function with setof but i want to know if it is possible to do it with a select _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Index question
"David Witham" <[EMAIL PROTECTED]> writes: > I understand that selecting count(*) will involve a scan at some > stage, but I was surprised that the index wasn't used in the >= case, > but was used in the between case. Given the estimated row counts in your examples, the planner's choices are not surprising. You have not given us any information on whether those estimates are accurate. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] multi results with functions
Hi Im trying to make a stored procedure that returns * from at table, and returns multible rows. Can you do that, and how? basically I would like to do: create or replace function test () returns record as ' select * from dummytable; ' language sql; Thanks - Kasper ---(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
Re: [SQL] sum ( time) problem
"Loeke" <[EMAIL PROTECTED]> wrote in Dan heb je nog de 24-uurs notatie, en krijg je niet een result boven de 24-uurs notatie. news:[EMAIL PROTECTED]: > kijk eens bij functions and operators van de > sql help bij pgadmin > > "Willem de Jong" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] >> Hello, >> >> If i do a sum(time) the result is like this '1 day 18:00:00'. But i'd >> like to get a result like this '42:00:00'. >> >> How can i realise is by a query? >> >> thanks in advance, >> >> Willem de Jong. > > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] not really SQL but I need info on BLOBs
I did something similar- a web application for online employer picture directory along with dynamic graphical output of the desk locations throughout the company (using the GD package). I kept the pictures in a separate directory and used links in the database. I set up a DB trigger that took care of the respective picture if an employer left. It worked great and was fantastic from a performance perspective. The only reason I could come up with keeping images in the DB as BLOBs is if you developed a specialized function that would do actual image analysis on the pictures themselves. For example, flagging certain images after weeding out faces of terrorists in a picture of a crowd of random people. But what do I know, I don't work at the Department of Homeland Security :-) Jared ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] type regclass casting
hello select * from pg_class where oid = 'sometablename'::regclass; (1 row) select * from pg_class where oid = 'sometablename'::text::regclass; ERROR can not cast TEXT to regclass What does this mean ? version 7.3.3 please try it on 7.4.x ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] multi results with functions
hi, kasper wrote, On 5/31/2004 00:02: Hi Im trying to make a stored procedure that returns * from at table, and returns multible rows. Can you do that, and how? basically I would like to do: create or replace function test () returns record as ' select * from dummytable; ' language sql; returns SETOF dummytable as ' C. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] insert or update violates foreign key constraint.why?
it seems to be a simple problem, but it is not. i have declared 2 simple tables: CREATE TABLE public.test1 ( id int4 NOT NULL, data float4, CONSTRAINT mytest_pkey PRIMARY KEY (id) ) WITH OIDS; CREATE TABLE public.test2 ( id1 int4 NOT NULL, data1 float4, CONSTRAINT test2_pkey PRIMARY KEY (id1), CONSTRAINT "$1" FOREIGN KEY (id1) REFERENCES public.test1 (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE ) WITH OIDS; then i have written a function in PLPGSQL: CREATE OR REPLACE FUNCTION public.test_func() RETURNS bool AS ' DECLARE mission RECORD; ret bool; BEGIN ret:=FALSE; raise notice\'begin\'; SET CONSTRAINTS ALL IMMEDIATE; FOR mission IN SELECT * FROM public.test1 LOOP raise notice\'before update\'; UPDATE public.test2 SET data1=data1+1; END LOOP; FOR mission IN SELECT * FROM public.test1 LOOP raise notice\'after update\'; DELETE FROM public.test1 WHERE id=mission.id; END LOOP; ret:=TRUE; raise notice\'end\'; RETURN ret; END;' LANGUAGE 'plpgsql' VOLATILE; my expecting behavior is: update all rows in test2 delete all from test1 und then delete all from test1 because of the ON DELETE CASCADE BUT: NOTICE: begin NOTICE: before update NOTICE: before update NOTICE: before update NOTICE: after update NOTICE: after update NOTICE: after update NOTICE: end ERROR: insert or update on table "test2" violates foreign key constraint "$1" DETAIL: Key (id1)=(1) is not present in table "test1". why? i have asked already in postgresql chan for help, but nobody could solve the problem. I hope you can. Big THX Oleg PS: i tried NOT DEFERRABLE too, it does not work too. :-( ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] insert or update violates foreign key constraint.why?
Oleg Mayevskiy <[EMAIL PROTECTED]> writes: > it seems to be a simple problem, but it is not. Yes it is ... you're expecting the RI triggers to fire during the plpgsql function, but in fact they fire at completion of the outer statement that called the plpgsql function. There's been some debate about whether this is really the most desirable behavior, but that's how it is at the moment. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] arrays and polygons
Hi there i am having problems inserting an array of polygons into a table, i
have added the column using:
ALTER TABLE species ADD COLUMN location polygon[];
this works fine, but when i try
INSERT INTO species (location) VALUES ('{((432,89), (45,87), (89,87)),
((54,78), (34,98))};
I get the following error message:
ERROR: Bad polygon external representation '((432'
Where am i going wrong, all help is much appreciated DAvid
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
