Re: [SQL] dynmic column names inside trigger?
Functionally it's the same. The difference is that you don't have to DECLARE a
variable for assembling your return value.
It's either
func(intext IN text, outtext OUT text returns NULL AS (
BEGIN
...
return;
END;
);
or
func(intext IN text) returns text AS (
DECLARE
outtext text;
BEGIN
...
return outtext;
END;
);
I find it especially handy when I want the function to return multiple values
(thus a record) which are not of a table type.
The second way you would have to define a type for your return values first and
than use it in your function
"My" way you would just write
func(intext IN text, outfield1 OUT text, outfield2 OUT date, outfield3 OUT
integer) returns NULL AS (...);
A third way is to define the return value(s) inside the function as type record
but than you have to name the output when you call the function
func(intext IN text) returns record AS (
DECLARE
rec record;
BEGIN
...
return rec;
END;
);
SELECT * FROM func('my input text') AS (field1 text, field2 date, field3
integer);
I find this less intuitive.
Buth in the end you just choose the technique you like best. I'm not aware of
any performance penalties for either technique. Anyone?
>>> Louis-David Mitterrand <[EMAIL PROTECTED]> 2007-11-23 18:06 >>>
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote:
> I would do something like this (not tested, but conceptually working):
>
> CREATE or replace FUNCTION sanitize_text(webtext IN text, cleantext OUT text)
> AS
> $body$
> BEGIN
> cleantext = translate(webtext, E'\x92\x96', '''-');
> cleantext = regexp_replace(cleantext, E'\x9c', 'oe', 'g');
> cleantext = regexp_replace(cleantext, E'\x85', '...', 'g');
> END;
> $body$
> LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT;
Hi,
I was curious as to why you created this function with a prototype of
func(intext IN text, outtext OUT text) ... returns NULL
instead of the usual
func(intext text) ... returns TEXT
Is that a more efficient way?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] PG trouble with index-usage in sort
Hi all. I'm seeing something fishy when trying to self-join two large tables and then order by one column. I have the following schema: CREATE TABLE origo_person( id SERIAL PRIMARY KEY, firstname varchar, lastname varchar, created timestamp not null default now(), created_by integer REFERENCES onp_user(id), onp_user_id integer references onp_user(id) ); create index origo_person_created_idx on origo_person(created); create index origo_person_createdby_idx on origo_person(created_by); create index origo_person_onp_user_id_idx on origo_person(onp_user_id); create index origo_person_firstname_idx on origo_person(firstname); insert into onp_user(id) values (1); copy origo_person (firstname, lastname, created) from '/home/andreak/simpleperson.sql'; update origo_person set created_by = 1; update origo_person set onp_user_id = 1 where id = 1; simpleperson.sql has more than 200K entries in COPY-format: $ tail -5 /home/andreak/simpleperson.sql INGOLF KALLEBERG 2007-08-21 22:23:43.571421 SIGRUNN BRUVIK 2007-08-21 22:23:43.571421 ELFRID FROGNER 2007-08-21 22:23:43.571421 GUNNAR KRISTOFFER DOVLAND 2007-08-21 22:23:43.571421 JAN ARNEHAARR 2007-08-21 22:23:43.571421 Now, the two first queries are *fast*, but the 3rd query is slow: 1. Fast: EXPLAIN ANALYZE SELECT p.firstname, p.lastname FROM origo_person p order by p.firstname ASC limit 5; QUERY PLAN - Limit (cost=0.00..0.55 rows=5 width=17) (actual time=0.031..0.070 rows=5 loops=1) -> Index Scan using origo_person_firstname_idx on origo_person p (cost=0.00..22277.13 rows=200827 width=17) (actual time=0.025..0.046 rows=5 loops=1) Total runtime: 0.128 ms (3 rows) 2. Fast: EXPLAIN ANALYZE SELECT p.firstname, p.lastname FROM origo_person p, origo_person pcb WHERE pcb.onp_user_id = p.created_by limit 5; QUERY PLAN - Limit (cost=0.00..31526.55 rows=1 width=17) (actual time=0.096..0.170 rows=5 loops=1) -> Merge Join (cost=0.00..31526.55 rows=1 width=17) (actual time=0.091..0.145 rows=5 loops=1) Merge Cond: (p.created_by = pcb.onp_user_id) -> Index Scan using origo_person_createdby_idx on origo_person p (cost=0.00..10697.70 rows=200827 width=21) (actual time=0.045..0.057 rows=5 loops=1) -> Index Scan using origo_person_onp_user_id_idx on origo_person pcb (cost=0.00..19824.70 rows=200827 width=4) (actual time=0.032..0.044 rows=5 loops=1) Total runtime: 0.264 ms (6 rows) 3. Slow: EXPLAIN ANALYZE SELECT p.firstname, p.lastname FROM origo_person p, origo_person pcb WHERE pcb.onp_user_id = p.created_by order by p.firstname ASC limit 5; QUERY PLAN - Limit (cost=31526.56..31526.56 rows=1 width=17) (actual time=2573.993..2574.015 rows=5 loops=1) -> Sort (cost=31526.56..31526.56 rows=1 width=17) (actual time=2573.987..2573.994 rows=5 loops=1) Sort Key: p.firstname Sort Method: top-N heapsort Memory: 17kB -> Merge Join (cost=0.00..31526.55 rows=1 width=17) (actual time=0.098..2047.726 rows=200827 loops=1) Merge Cond: (p.created_by = pcb.onp_user_id) -> Index Scan using origo_person_createdby_idx on origo_person p (cost=0.00..10697.70 rows=200827 width=21) (actual time=0.052..428.445 rows=200827 loops=1) -> Index Scan using origo_person_onp_user_id_idx on origo_person pcb (cost=0.00..19824.70 rows=200827 width=4) (actual time=0.031..424.250 rows=200828 loops=1) Total runtime: 2574.113 ms (9 rows) Can anybody point out to me why PG doesn't perform better on the last query? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] materialize
Greetings,
I was wondering why do we need the Materialize node in the plan below when i
explain a query?
1: QUERY PLAN = "Nested Loop (cost=10.99..24.34 rows=1 width=846)"(typeid
= 25, len = -1, typmod = -1, byval = f)
1: QUERY PLAN = " Join Filter: (("inner".cover)::text =
("outer".cover)::text)"(typeid = 25, len = -1, typmod = -1, byval = f)
1: QUERY PLAN = " -> Index Scan using idx_cover_ftn_ on c_cover_ftn
(cost=0.00..9.30 rows=2 width=86)"(typeid = 25, len = -1, typmod = -1,
byval = f)
1: QUERY PLAN = " Index Cond: (username = 'user1'::name)"
(typeid = 25, len = -1, typmod = -1, byval = f)
1: QUERY PLAN = " -> Materialize (cost=10.99..11.89 rows=90 width=842)"
(typeid = 25, len = -1, typmod = -1, byval = f)
1: QUERY PLAN = " -> Seq Scan on books (cost=0.00..10.90
rows=90 width=842)"(typeid = 25, len = -1, typmod = -1, byval = f)
Thanks,
Ehab
_
Your smile counts. The more smiles you share, the more we donate. Join in.
www.windowslive.com/smile?ocid=TXT_TAGLM_Wave2_oprsmilewlhmtagline
Re: [SQL] materialize
Ehab Galal <[EMAIL PROTECTED]> writes: > I was wondering why do we need the Materialize node in the plan below when i > explain a query? It's cheaper to scan a materialized rowset many times than a raw table --- we don't need to re-access shared memory nor re-check row visibility. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
