[SQL] return setof record - strange behavior
Hi everybody. Can anyone enlighten me what's wrong with this function : CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer, OUT ro integer, OUT mi integer) RETURNS SETOF record AS $BODY$ DECLARE w record; cy integer := EXTRACT (YEAR FROM current_date); BEGIN FOR w IN SELECT (CASE WHEN m 12 THEN cy + 1 ELSE cy END)::integer, (CASE WHEN m 12 THEN m - 12 ELSE m END)::integer FROM generate_series(mon + 1, mon + intv) AS m LOOP RETURN next; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; SELECT * FROM month_year(10, 5); Why does it return empty SET ? The amount of rows is correct though I'm running 8.1.4 regards mk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] return setof record - strange behavior
The function behaves as expected when in plain SQL, only plpgsql function has the above mentioned problem. regards mk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] return setof record - strange behavior
2008/8/4 Marcin Krawczyk [EMAIL PROTECTED]
Hi everybody. Can anyone enlighten me what's wrong with this function :
CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer, OUT
ro integer, OUT mi integer)
RETURNS SETOF record AS
$BODY$
DECLARE
w record;
cy integer := EXTRACT (YEAR FROM current_date);
BEGIN
FOR w IN
SELECT (CASE WHEN m 12 THEN cy + 1 ELSE cy END)::integer, (CASE
WHEN m 12 THEN m - 12 ELSE m END)::integer
FROM generate_series(mon + 1, mon + intv) AS m
LOOP
RETURN next;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
SELECT * FROM month_year(10, 5);
Why does it return empty SET ? The amount of rows is correct though
I'm running 8.1.4
regards
mk
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Hi
merlin=# CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer)
RETURNS SETOF record AS
$BODY$
DECLARE
w record;
cy integer := EXTRACT (YEAR FROM current_date);
BEGIN
FOR w IN
SELECT (CASE WHEN m 12 THEN cy + 1 ELSE cy END)::integer, (CASE
WHEN m 12 THEN m - 12 ELSE m END)::integer
FROM generate_series(mon + 1, mon + intv) AS m
LOOP
RETURN next w;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
and
merlin=# SELECT * FROM month_year(10, 5) as (x integer, y integer);
x | y
--+
2008 | 11
2008 | 12
2009 | 1
2009 | 2
2009 | 3
(5 rows)
without output params
--
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2**y% -;^[%`-{
a%%s%%$_%ee'
Re: [SQL] return setof record - strange behavior
Dzieki za odpowiedz. Ciekawe ze funkcja SQL dziala bez problemu - ale
tu juz trzeba wskazac parametry OUT.
Thanks for your answer. It's curious that SQL function works as
expected - but requires OUT params.
pozdrowienia/regards
mk
2008/8/4 Pawel Socha [EMAIL PROTECTED]:
2008/8/4 Marcin Krawczyk [EMAIL PROTECTED]
Hi everybody. Can anyone enlighten me what's wrong with this function :
CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer, OUT
ro integer, OUT mi integer)
RETURNS SETOF record AS
$BODY$
DECLARE
w record;
cy integer := EXTRACT (YEAR FROM current_date);
BEGIN
FOR w IN
SELECT (CASE WHEN m 12 THEN cy + 1 ELSE cy END)::integer, (CASE
WHEN m 12 THEN m - 12 ELSE m END)::integer
FROM generate_series(mon + 1, mon + intv) AS m
LOOP
RETURN next;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
SELECT * FROM month_year(10, 5);
Why does it return empty SET ? The amount of rows is correct though
I'm running 8.1.4
regards
mk
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Hi
merlin=# CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer)
RETURNS SETOF record AS
$BODY$
DECLARE
w record;
cy integer := EXTRACT (YEAR FROM current_date);
BEGIN
FOR w IN
SELECT (CASE WHEN m 12 THEN cy + 1 ELSE cy END)::integer, (CASE
WHEN m 12 THEN m - 12 ELSE m END)::integer
FROM generate_series(mon + 1, mon + intv) AS m
LOOP
RETURN next w;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
and
merlin=# SELECT * FROM month_year(10, 5) as (x integer, y integer);
x | y
--+
2008 | 11
2008 | 12
2009 | 1
2009 | 2
2009 | 3
(5 rows)
without output params
--
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2**y% -;^[%`-{
a%%s%%$_%ee'
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Case Insensitive searches
In the application that we are working on, all data searches must be case insensitive. Select * from test where column1 = 'a' and Select * from test where column1 = 'A' should always be the same and use the index if column1 is indexed. In order to do this am I going to be required to use the lower() on all selects in order to make sure that they are case insensitive? In some db's if you use a lower() or upr() it will always do a table scan instead of using a index Best Regards, Michael Gould, Manager Information Technology All Coast Intermodal Services, Inc. First Coast Intermodal Services, Inc. First Coast Logistical Services, LLC. 904-226-0978
Re: [SQL] return setof record - strange behavior
Pawel Socha [EMAIL PROTECTED] writes: 2008/8/4 Marcin Krawczyk [EMAIL PROTECTED] Hi everybody. Can anyone enlighten me what's wrong with this function : [ you didn't do RETURN next w; ] IIRC the other way is to assign to the output parameters by name, then do RETURN NEXT with no argument. But the FOR-loop all by itself isn't going to return any data to the function's caller. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Case Insensitive searches
On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould [EMAIL PROTECTED] wrote: In some db's if you use a lower() or upr() it will always do a table scan instead of using a index True, this would also happen in PostgreSQL. However, you can overcome this by creating a functional index: http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html This way all expression using where lower( column ) = 'a'. will always use an index scan. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Case Insensitive searches
On Monday 04 August 2008 10:05, Richard Broersma wrote: On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould [EMAIL PROTECTED] wrote: In some db's if you use a lower() or upr() it will always do a table scan instead of using a index True, this would also happen in PostgreSQL. However, you can overcome this by creating a functional index: http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html This way all expression using where lower( column ) = 'a'. will always use an index scan. -- What about using the operator, ~* ? Does that cause a table scan as well? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Case Insensitive searches
Terry Lee Tucker wrote: On Monday 04 August 2008 10:05, Richard Broersma wrote: On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould [EMAIL PROTECTED] wrote: In some db's if you use a lower() or upr() it will always do a table scan instead of using a index True, this would also happen in PostgreSQL. However, you can overcome this by creating a functional index: http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html This way all expression using where lower( column ) = 'a'. will always use an index scan. What about using the operator, ~* ? Does that cause a table scan as well? Whether or not any query uses an index scan or seq scan depends on many factors and is not always easily predictable. Richard's statement about will always use an index scan is not universally true. If the table is very small; a index scan is NOT used. Table statistics could also indicate a seq scan is more efficient (suppose 99% of rows had column='a'). The ~* operator is very likely to scan the entire table because it will look for 'A' anywhere in the column (and will therefore match 'Joanne'; and I doubt that there is special code to handle case where length of argument is exactly the same as column. However; ~* '^a' which anchors search to first character is perhaps more likely to use an index scan. Frank -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Case Insensitive searches
On Monday 04 August 2008 11:09, Frank Bax wrote: Terry Lee Tucker wrote: On Monday 04 August 2008 10:05, Richard Broersma wrote: On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould [EMAIL PROTECTED] wrote: In some db's if you use a lower() or upr() it will always do a table scan instead of using a index True, this would also happen in PostgreSQL. However, you can overcome this by creating a functional index: http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html This way all expression using where lower( column ) = 'a'. will always use an index scan. What about using the operator, ~* ? Does that cause a table scan as well? Whether or not any query uses an index scan or seq scan depends on many factors and is not always easily predictable. Richard's statement about will always use an index scan is not universally true. If the table is very small; a index scan is NOT used. Table statistics could also indicate a seq scan is more efficient (suppose 99% of rows had column='a'). The ~* operator is very likely to scan the entire table because it will look for 'A' anywhere in the column (and will therefore match 'Joanne'; and I doubt that there is special code to handle case where length of argument is exactly the same as column. However; ~* '^a' which anchors search to first character is perhaps more likely to use an index scan. Frank Frank, Thanks for the response. Actually, from within the applicaion, we use ~* and it is anchored with whatever they've typed in the widget as search criteria. Anyway, thanks for the helpful response... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] fast insert-if-key-not-already-there
Hi! I have to do much inserts into a database where the key most often is already there. My current approach is to query for the key (ip-address), and if the result is null I do the insert. For every IP-Address I need the ip_addr_id from the same table. Something like this: CREATE TABLE ip_addresses ( ip_addr_idserial NOT NULL, ip_addr inetUNIQUE NOT NULL PRIMARY KEY(ip_addr); ); CREATE OR REPLACE FUNCTION update_Addresses( v_ip_addresses inet[] ) RETURNS void AS $$ DECLARE v_ip_addr INET; v_ip_addr_idINTEGER := 0 ; v_ip_addr_ids INTEGER[]; BEGIN FOR i IN 1..( array_upper( v_ip_addresses, 1 ) )::integer LOOP v_ip_addr = v_ip_addresses[i]; -- check if ip_addr exists and append if not SELECT ip_addr_id FROM ip_addresses WHERE ip_addr=v_ip_addr INTO v_ip_id; IF v_ip_id IS NULL THEN INSERT INTO ip_addresses ( ip_addr ) VALUES( v_ip_addr ) RETURNING ip_addr_id INTO v_ip_id ; END IF; v_ip_addr_ids = array_append(v_ip_addr_ids, v_ip_addr_id); END LOOP; END; $$ LANGUAGE 'plpgsql' STRICT; Now I'm wondering if there is a better solution, since I'm doing ~20 inserts at once and every time I'm doing single lookup's for the IDs. regards patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] fast insert-if-key-not-already-there
Patrick Scharrenberg, 04.08.2008 17:51: Hi! I have to do much inserts into a database where the key most often is already there. My current approach is to query for the key (ip-address), and if the result is null I do the insert. For every IP-Address I need the ip_addr_id from the same table. [...] Now I'm wondering if there is a better solution, since I'm doing ~20 inserts at once and every time I'm doing single lookup's for the IDs. If you know that most of the time the record is already there, I simply execute the UPDATE, then check how many rows were updated. If that returns zero, I'll send the INSERT Regards Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] fast insert-if-key-not-already-there
On Mon, Aug 4, 2008 at 8:51 AM, Patrick Scharrenberg [EMAIL PROTECTED] wrote: My current approach is to query for the key (ip-address), and if the result is null I do the insert. For every IP-Address I need the ip_addr_id from the same table. INSERT INTO ip_addresses ( ip_addr ) VALUES( v_ip_addr ) RETURNING ip_addr_id INTO v_ip_id ; another option is to only insert if the addresses if they do not yet exist. You might have to rethink some of your other logic however: INSERT INTO Ip_addresses ( ip_addr ) SELECT ip_addr FROM ( VALUES ( v_ip_addr )) AS A( ip_addr ) LEFT JOIN Ip_addresses AS B ON A.ip_addr = B.ip_addr WHERE B.ip_addr IS NULL; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
