Re: [SQL] plpgsql vs. SQL in stored procedures
[EMAIL PROTECTED] (Ivan Sergio Borgonovo) wrote: > Is there a way to declare variables and use IF in plain SQL, not in > plpgsql inside stored procedures? The forthcoming support for recursive queries using a WITH clause might provide, after a fashion, a way to declare variables. As for IF, there is a functional equivalent to it in the form of the SQL standard CASE statement. -- select 'cbbrowne' || '@' || 'ntlug.org'; http://www.ntlug.org/~cbbrowne/linuxxian.html "How should I know if it works? That's what beta testers are for. I only coded it." (Attributed to Linus Torvalds, somewhere in a posting) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Timestamp manipulation
I am having a bit of difficulty trying to find a solution to this problem of manipulating timestamps and dates so I hope someone can enlighten me. I have a table which contains a column of type timestamp. For each row I want to find out the dates for the beginning and end of the week within which the timestamp lies. e.g. 2004-04-09 16:00:00+01 The beginning of the week (sunday) is: 2004-04-4 and the end of the week (saturday) is: 2004-04-10 Any suggestions as to how to go about this would be great, thanks in advance. Stephen Quinney ---(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] plpgsql vs. SQL in stored procedures
On Wed, 31 Mar 2004 03:31:01 -0500 Christopher Browne <[EMAIL PROTECTED]> wrote: > The forthcoming support for recursive queries using a WITH clause > might provide, after a fashion, a way to declare variables. I think I'll have to work with pg 7.3 Does this translate to: you won't be able to use variables? I was used to do stuff like this with MS SQL create proc sp_getuid @uid uniqueidentifier as delete from auth where lapsedateadd(mi,-10,getdate()) and [EMAIL PROTECTED]) if (@R_ID is null) begin delete from auth where [EMAIL PROTECTED] select null as R_ID end else begin update auth set lapse=getdate() where [EMAIL PROTECTED] select @R_ID as R_ID end Does it mean that to have variables in SP I'll have to use plpgsql in spite of plain SQL? > As for IF, there is a functional equivalent to it in the form of the > SQL standard CASE statement. TY for the refreshing pointer. I was looking in the wrong place. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql vs. SQL in stored procedures
On Wednesday 31 March 2004 12:07, Ivan Sergio Borgonovo wrote: > > I was used to do stuff like this with MS SQL > > create proc sp_getuid @uid uniqueidentifier [snip] > Does it mean that to have variables in SP I'll have to use plpgsql > in spite of plain SQL? Yep - just like there you were using transact-sql (if I remember its name correctly) rather than sql. The syntax is different (plpgsql bares a startling resemblance to the Oracle approach), the purpose of each is the same. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Is it normal that functions are so much faster than inline queries
I'm doing some test on our PgSQL 7.3.4 and I can't believe what I see: When I want to execute this set of queries in a function: DELETE FROM oly.amaze_log_report WHERE batch = $1 ; INSERT INTO oly.amaze_log_report SELECT $1, 'DATA', 'MISSING_NEWREF_DECLARATION', 'ERROR', tmp.error_count, 'Missing NEW reference declaration for ' || tmp.class_name || '.' || tmp.feature_name FROM ( SELECT count (DISTINCT LDO.new_value) AS error_count, LDO.class AS class_name, LDO.feature AS feature_name FROM oly.amaze_log_database_object AS LDO LEFT JOIN oly.amaze_log_object AS LO ON ((LO.batch, LO.id) = (LDO.batch, LDO.new_value)) WHERE LDO.batch = $1 AND LO.batch IS NULL GROUP BY LDO.class, LDO.feature ) AS tmp WHERE tmp.error_count > 0 ; SELECT count(*) AS error_count FROM oly.amaze_log_report WHERE batch = $1; It takes only 2 seconds. But when I tried to do it directly in the psql term (replacing the $1 value with the same used in the function call), I'm obliged to kill the second query after 10 minutes because it's still runnning! I'm really wondering why the functions are so fast comparing to the classical SQL statement. Any ideas? -- Downloading signature ... 99% *CRC FAILED* signature aborted ---(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] Is it normal that functions are so much faster than inline queries
"Olivier Hubaut" <[EMAIL PROTECTED]> writes: > When I want to execute this set of queries in a function: > ... > It takes only 2 seconds. > But when I tried to do it directly in the psql term (replacing the $1 > value with the same used in the function call), I'm obliged to kill the > second query after 10 minutes because it's still runnning! You're presumably getting different plans in the two cases. Usually we hear complaints about the function case being slower, because the planner has less information when it has to work with a parameter instead of a constant. In this case it seems the stupider plan is being chosen with a constant :-(. You have not shown enough information to tell why, but I'm wondering about datatype mismatch preventing an index from being used. What is the declared datatype of the $1 parameter, and does it match what will be assumed for the unadorned constant? regards, tom lane ---(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] left join on a view takes significantly more time.
Hi,
I'm facing a wired problem. When I left join two tables PostgreSQL is
able to do it fast using the corresponding indices, However, if I
define a simple view (to format the data) on one of the tables, the
left join does not use the indices. Is something wrong here?
The two tables:
\d regacd.profesor
Tabla "regacd.profesor"
Columna|Tipo | Modificadores
--+-+---
id | integer | not null default
nextval('regacd.profesor_sid'::text)
grupo_id | integer | not null
tipo_id | "char" | not null
cargo_id | integer | not null
académico_id | integer |
última_actualización | timestamp without time zone | default now()
Índices:
"profesor_pkey" llave primaria, btree (id)
"profesor_académico" btree ("académico_id")
"profesor_grupo" btree (grupo_id)
Restricciones de llave foránea:
"CargoTipoId" FOREIGN KEY (cargo_id, tipo_id) REFERENCES cargo(id, tipo_id)
"IdentificadorGrupoTipoId" FOREIGN KEY (grupo_id, tipo_id) REFERENCES
regacd.grupo(id, tipo_id)
"$1" FOREIGN KEY ("académico_id") REFERENCES personal(id)
Triggers:
"profesor_última_actualización" BEFORE INSERT OR UPDATE ON regacd.profesor FOR
EACH ROW EXECUTE PROCEDURE "profesor_última_actualización"()
"propaga_actualización_profesor" AFTER INSERT OR UPDATE ON regacd.profesor FOR
EACH ROW EXECUTE PROCEDURE "propaga_actualización_profesor"()
"update_datos_académico" BEFORE INSERT OR UPDATE ON regacd.profesor FOR EACH ROW
EXECUTE PROCEDURE "update_datos_académico"()
\d ordinario.horario
Tabla "ordinario.horario"
Columna|Tipo |
Modificadores
--+-+-
id | integer | not null default
nextval('ordinario.horario_sid'::text)
profesor_id | integer |
lu | boolean | not null default false
ma | boolean | not null default false
mi | boolean | not null default false
ju | boolean | not null default false
vi | boolean | not null default false
sá | boolean | not null default false
hora_inicial | time without time zone |
hora_final | time without time zone |
salón_id | integer |
nota | text|
última_actualización | timestamp without time zone | default now()
Índices:
"horario_pkey" llave primaria, btree (id)
"horario_profesor" btree (profesor_id)
Restricciones de llave foránea:
"$2" FOREIGN KEY ("salón_id") REFERENCES "salón"(id)
"$1" FOREIGN KEY (profesor_id) REFERENCES regacd.profesor(id) ON UPDATE CASCADE ON
DELETE CASCADE
Triggers:
"horario_última_actualización" BEFORE INSERT OR UPDATE ON ordinario.horario FOR
EACH ROW EXECUTE PROCEDURE "horario_última_actualización"()
"propaga_actualización_horario" AFTER INSERT OR UPDATE ON ordinario.horario FOR
EACH ROW EXECUTE PROCEDURE "propaga_actualización_horario"()
Now, a left join query of the tables leads a nice and fast plan:
explain analyze select * from regacd.profesor p left join ordinario.horario h on
(h.profesor_id = p.id) where p.grupo_id IN (129314, 129315, 129316, 129317, 129318,
129319, 129320, 129321, 129322);
QUERY PLAN
-
Nested Loop Left Join (cost=0.00..150.79 rows=18 width=78) (actual time=0.400..1.232
rows=19 loops=1)
-> Index Scan using profesor_grupo, profesor_grupo, profesor_grupo,
profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo, profesor_grupo,
profesor_grupo on profesor p (cost=0.00..96.50 rows=18 width=25) (actual
time=0.231..0.499 rows=19 loops=1)
Index Cond: ((grupo_id = 129314) OR (gr
Re: [SQL] SQL Spec Compliance Questions
Tom, > This looks more like an underling with a checklist than a serious > inquiry. Can you get them to specify particular capabilities that they > need? In sufficient detail that we could actually answer? Ok, talked with him. They are trying to plan OO-->DB mapping in 3 programming languages for a large project. Large enough that they would cost out implementing these SQL99 features for us if they like PostgreSQL otherwise.But they *do* need to complete the checklist for each candidate database system. So ... can anyone more familiar with SQL99 than me give some feedback? -- Josh Berkus Aglio Database Solutions San Francisco ---(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] left join on a view takes significantly more time.
Manuel, > I'm facing a wired problem. When I left join two tables PostgreSQL is > able to do it fast using the corresponding indices, However, if I > define a simple view (to format the data) on one of the tables, the > left join does not use the indices. Is something wrong here? At a guess, the complex CASE statements are causing the planner to behave badly. What happens if you do a straight join and not a left join? Also, it's generally a good idea to put your join expression in the same order as the referenced tables. You confused me and you could confuse the planner at some stage. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
