Re: [SQL] Aggregate Functions Template
Yes, your varlena links are what I was looking for as a source of help... Thanks! Mark Michael Fuhr wrote: On Thu, May 19, 2005 at 03:17:07PM -0400, Mark Fenbers wrote: I need to create an aggregate function to do some math not currently provided by the available tools. Can someone point to an example aggregate function syntax that I can use as a template for my own function. I'm still a little green on some aspects of PostgreSQL and am drawing a blank on how to do this properly from scratch. The General Bits newsletter has a few examples that might be helpful, even if they're not quite what you're after: http://www.varlena.com/varlena/GeneralBits/109.php http://www.varlena.com/varlena/GeneralBits/4.html There are sure to be some examples in the list archives -- just search for create aggregate: http://archives.postgresql.org/ If these links don't help, then please post more details about what you're trying to do and what trouble you're having. If you have any code that doesn't work the way you want but that helps show what you're after, then go ahead and post it with an explanation of what it does (or doesn't do) and what you'd like it to do (or not do). begin:vcard fn:Mark Fenbers n:Fenbers;Mark org:DoC/NOAA/NWS/OHRFC adr:;;1901 South SR 134;Wilmington;OH;45177-9708;USA email;internet:[EMAIL PROTECTED] title:Sr. HAS Meteorologist tel;work:937-383-0430 x246 x-mozilla-html:TRUE url:http://weather.gov/ohrfc version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Transaction in plpgslq
I have got a plpgsql function: CREATE FUNCTION nueva_llamada(integer, integer) RETURNS integer as DECLARE _operadora_id ALIAS FOR $1; _actividad_id ALIAS FOR $2; _contacto_id integer; BEGIN -- BEGIN; SELECT min(id) INTO _contacto_id FROM contactos WHERE contactos.operadora_id IS NULL AND contactos.actividad_id = _actividad_id; UPDATE contactos SET operadora_id = _operadora_id WHERE id = _contacto_id; -- COMMIT; INSERT INTO llamadas (contacto_id, operadora_id, fecha) VALUES (_contacto_id, _operadora_id, now()); RETURN _contacto_id; END and it works right, but I need atomic execution from --BEGIN and --COMMIT, and manual says it is not possible to have transactions in PL/pgSQL procedures :-( May be with LOCK TABLE? -- Rafa Couto (caligari) mailto:[EMAIL PROTECTED] urgentes (sólo texto): [EMAIL PROTECTED] PGP 0x30EC5C31 [E6BF 11EF FE55 38B1 CF7E 9380 58E5 9FA3] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How do I quit in the middle of a SQL script?
Say if I want to add a small snip of code in front of the sql script generated by the pg_dump, to check for something then if the condition doesn't match, the script terminates right away. (Without actually doing the restoring stuff that the following large chunk is supposed to do) Can I do that? And is it a good idea to add arbitrary code to the database dump sql script? Thanks! Wei ---(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
Re: [SQL] Transaction in plpgslq
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The manual is correct, you can not do transactions within a procedure since the procedure must be called within a transaction. If you're working with postgres 8, you can achieve similar functionality using checkpoints. But that won't solve the problem you have below. The solution to your problem is locking (or concurrency control if you prefer). While we're at it, we might as well optimize your statement a little too using ORDER BY with LIMIT instead of min(). SELECT id INTO _contacto_id FROM contactos WHERE contactos.operadora_id IS NULL AND contactos.actividad_id = _actividad_id ORDER BY id LIMIT 1 FOR UPDATE; Take a look at the FOR UPDATE section of the SELECT description for an explanation of how this works. http://www.postgresql.org/docs/8.0/static/sql-select.html If you still have questions, then you might want to take a look at the concurrency control section of the manual. http://www.postgresql.org/docs/8.0/static/mvcc.html - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A Rafa Couto wrote: I have got a plpgsql function: CREATE FUNCTION nueva_llamada(integer, integer) RETURNS integer as DECLARE _operadora_id ALIAS FOR $1; _actividad_id ALIAS FOR $2; _contacto_id integer; BEGIN -- BEGIN; SELECT min(id) INTO _contacto_id FROM contactos WHERE contactos.operadora_id IS NULL AND contactos.actividad_id = _actividad_id; UPDATE contactos SET operadora_id = _operadora_id WHERE id = _contacto_id; -- COMMIT; INSERT INTO llamadas (contacto_id, operadora_id, fecha) VALUES (_contacto_id, _operadora_id, now()); RETURN _contacto_id; END and it works right, but I need atomic execution from --BEGIN and --COMMIT, and manual says it is not possible to have transactions in PL/pgSQL procedures :-( May be with LOCK TABLE? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFCjiRXgfzn5SevSpoRAlZRAJ4pg7UohNBy+RhgoOfbqy0W9wbIXQCff6F1 VEPjPfo4tSxn+kMg6snBbSI= =bzri -END PGP SIGNATURE- ---(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
Re: [SQL] Does Postgresql have a similar pseudo-column ROWNUM as
On Tue, 17 May 2005 12:01:03 -0500, Scott Marlowe wrote
On Thu, 2005-05-12 at 14:07, [EMAIL PROTECTED] wrote:
Hi:
Oracle has a pseudo-column ROWNUM to return the sequence
number in which a row was returned when selected from a table.
The first row ROWNUM is 1, the second is 2, and so on.
Does Postgresql have a similar pseudo-column ROWNUM as
Oracle? If so, we can write the following query:
select *
from (select RowNum, pg_catalog.pg_proc.*
from pg_catalog.pg_proc) inline_view
where RowNum between 100 and 200;
You can get a functional equivalent with a temporary sequence:
create temp sequence rownum;
select *, nextval('rownum') as rownum from sometable;
---(end of broadcast)---
TIP 8: explain analyze is your friend
Scott,
I realize that this thread went off in another direction however your
suggestion proved very helpful for a problem that I was trying to solve. I
wanted the row number of a set returned by a function. Here is a chopped
version of the function that I wrote.
CREATE OR REPLACE FUNCTION func_bom(integer, integer)
RETURNS SETOF func_bom AS
$BODY$
DECLARE
v_number ALIAS FOR $1;
v_line ALIAS FOR $2;
v_type varchar(8);
r_row interface.func_so_line_bom%rowtype;
BEGIN
SELECT tbl_item.item_type INTO v_type
FROM tbl_line_item
JOIN tbl_item
ON tbl_line_item.item_id = tbl_item.id
WHERE tbl_line_item.number = v_number
AND tbl_line_item.line = v_line;
IF v_type = 'ASY' THEN
CREATE TEMP SEQUENCE row_number
INCREMENT BY 1
START WITH 1;
FOR r_row IN SELECT tbl_line_item.number,
tbl_line_item.line,
nextval('row_number') AS subline,
tbl_assembly.quantity AS bom_quantity,
tbl_assembly.component_id AS bom_item_id,
tbl_item.item_type AS bom_item_type,
tbl_item.description AS bom_item_description
FROM tbl_line_item
LEFT JOIN tbl_assembly
ON ( tbl_line_item.item_id::text =
tbl_assembly.id::text
)
JOIN tbl_item
ON ( tbl_assembly.component_id::text =
tbl_item.id::text
)
WHERE tbl_line_item.number = v_number
AND tbl_line_item.line = v_line
ORDER BY tbl_line_item.number,
tbl_line_item.line,
tbl_assembly.component_id
LOOP
RETURN NEXT r_row;
END LOOP;
DROP SEQUENCE row_number;
ELSIFv_item_type = 'THIS'
OR v_item_type = 'THAT'
OR v_item_type = 'OTHER' THEN
FOR r_row IN SELECT
[snip]
LOOP
RETURN NEXT r_row;
END LOOP;
END IF;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
Although I have no need to limit the output I tried it just for giggles and it
worked fine.
SELECT * FROM func_bom(12345, 1) WHERE subline between 4 AND 6;
Thanks!
Kind Regards,
Keith
---(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] How do I quit in the middle of a SQL script?
On May 20, 2005, at 1:22 PM, Wei Weng wrote: Say if I want to add a small snip of code in front of the sql script generated by the pg_dump, to check for something then if the condition doesn't match, the script terminates right away. (Without actually doing the restoring stuff that the following large chunk is supposed to do) Can I do that? Put this at the start of the file to make psql stop if there is an error: \set ON_ERROR_STOP 1 And is it a good idea to add arbitrary code to the database dump sql script? No problem if you know what you are doing and/or have good backups :) John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Does Postgresql have a similar pseudo-column ROWNUM as
On Fri, 2005-05-20 at 13:27, Keith Worthington wrote: Scott, I realize that this thread went off in another direction however your suggestion proved very helpful for a problem that I was trying to solve. I wanted the row number of a set returned by a function. Here is a chopped version of the function that I wrote. CREATE OR REPLACE FUNCTION func_bom(integer, integer) RETURNS SETOF func_bom AS SNIP Although I have no need to limit the output I tried it just for giggles and it worked fine. SELECT * FROM func_bom(12345, 1) WHERE subline between 4 AND 6; You're welcome. I've saved that off to my ~/pgsql directory for future use. So, the thanks are back to you. :) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DBD::Pg on Enterprise 3
Mark Fenbers [EMAIL PROTECTED] writes: A colleage of mine in another office has RedHat Enterprise 3 installed. We do not have this yet, but will in the fall. According to him, the DBD::Pg module that has been a part of the Red Hat baseline from Redhat 7.2 (or earlier) through RH Fedora Core has been removed from RH Enterprise 3 baseline. AFAICT it's shipped on the CDs. It may well not be part of the minimal installation ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How do I quit in the middle of a SQL script?
Quoting John DeSoi [EMAIL PROTECTED]: On May 20, 2005, at 1:22 PM, Wei Weng wrote: Say if I want to add a small snip of code in front of the sql script generated by the pg_dump, to check for something then if the condition doesn't match, the script terminates right away. (Without actually doing the restoring stuff that the following large chunk is supposed to do) Can I do that? Put this at the start of the file to make psql stop if there is an error: \set ON_ERROR_STOP 1 And is it a good idea to add arbitrary code to the database dump sql script? No problem if you know what you are doing and/or have good backups :) You don't have to: pg_restore mydb.dump | psql --set ON_ERROR_STOP=1 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
