[SQL] pl/pgsql problem with return types
Hello! i have problem with pl/pgsql function; === ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function "tests" line 6 at return next === -- Function: tests(int8) -- DROP FUNCTION tests(int8); CREATE OR REPLACE FUNCTION tests(int8) RETURNS SETOF pages AS $BODY$DECLARE PRECORD; BEGIN FOR P IN select pageid from pages LOOP RETURN NEXT P; END LOOP; RETURN; END;$BODY$ LANGUAGE 'plpgsql' STABLE; ALTER FUNCTION tests(int8) OWNER TO diglat_web; -- Table: pages -- DROP TABLE pages; CREATE TABLE pages ( pageid int8 NOT NULL, ppageid int8 NOT NULL DEFAULT 0, name varchar(100), status int4 DEFAULT 0, CONSTRAINT pages_pkey PRIMARY KEY (pageid), CONSTRAINT pages_in_pages_fkey FOREIGN KEY (ppageid) REFERENCES pages (pageid) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT pages_uniq UNIQUE (pageid, ppageid) ) WITH OIDS; Actualy function is correct... and the same code run successfully on other table.. there is the problem ? Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] more execution time
why this query needs more time? Its very slow
thx
//QUERY
select
coalesce(personaldetails.masterid::numeric,personaldetails.id) +
(coalesce(personaldetails.id::numeric,0)/100) as sorting,
floor(coalesce(personaldetails.masterid::numeric,personaldetails.id) +
(coalesce(personaldetails.id::numeric,0)/100)) as ppid,
personaldetails.id as pid,
personaldetails.masterid,
coalesce(personaldetails.prefix,'') || '' ||
coalesce(personaldetails.firstname,' ') || ' ' ||
coalesce(personaldetails.lastname,) as fullname,
personaldetails.regtypeid,
personaldetails.regdate,
personaldetails.regprice,
coalesce(regtypes.regtype,' ') || ' ' ||
coalesce(regtypes.subregtype,' ') as regtypetitle,
regtypes.regtype,
regtypes.subregtype,
regtypedates.title,
balance('MASTER-REGISTRATION',personaldetails.id) as balance,
coalesce(pd2.prefix,' ') || ' ' || coalesce(pd2.firstname,' ') ||
' ' || coalesce(pd2.lastname,' ') as accfullname,
coalesce(rt2.regtype,) || ' ' || coalesce(rt2.subregtype,' ') as
accregtypetitle,
pd2.id as accid,
pd2.regtypeid as accregtypeid,
pd2.regdate as accregdate,
pd2.regprice as accregprice,
rt2.regtype as accregtype,
rt2.subregtype as accsubregtype,
rd2.title as acctitle,
balance('MASTER-REGISTRATION',pd2.id) as accbalance,
case when coalesce(balance('REGISTRATION',personaldetails.id),0)<=0
then 1 else 0 end as balancestatus
from personaldetails
left outer join regtypes on regtypes.id=personaldetails.regtypeid
left outer join regtypedates on regtypes.dateid=regtypedates.id
left outer join personaldetails pd2 on personaldetails.id=pd2.masterid
left outer join regtypes rt2 on rt2.id=pd2.regtypeid
left outer join regtypedates rd2 on rt2.dateid=rd2.id
where personaldetails.masterid is null
/ RESULT STATISTICS
Total query runtime: 348892 ms.
Data retrieval runtime: 311 ms.
763 rows retrieved.
EXPLAIN QUERY
Hash Left Join (cost=109.32..109.95 rows=5 width=434)
Hash Cond: ("outer".dateid = "inner".id)
-> Merge Left Join (cost=108.27..108.46 rows=5 width=409)
Merge Cond: ("outer".regtypeid = "inner".id)
-> Sort (cost=106.19..106.20 rows=5 width=347)
Sort Key: pd2.regtypeid
-> Hash Left Join (cost=90.11..106.13 rows=5 width=347)
Hash Cond: ("outer".id = "inner".masterid)
-> Hash Left Join (cost=45.49..45.71 rows=5 width=219)
Hash Cond: ("outer".dateid = "inner".id)
-> Merge Left Join (cost=44.44..44.63 rows=5
width=194)
Merge Cond: ("outer".regtypeid = "inner".id)
-> Sort (cost=42.36..42.37 rows=5
width=132)
Sort Key: personaldetails.regtypeid
-> Seq Scan on personaldetails
(cost=0.00..42.30 rows=5 width=132)
Filter: (masterid IS NULL)
-> Sort (cost=2.08..2.16 rows=31 width=66)
Sort Key: regtypes.id
-> Seq Scan on regtypes
(cost=0.00..1.31 rows=31 width=66)
-> Hash (cost=1.04..1.04 rows=4 width=33)
-> Seq Scan on regtypedates
(cost=0.00..1.04 rows=4 width=33)
-> Hash (cost=42.30..42.30 rows=930 width=132)
-> Seq Scan on personaldetails pd2
(cost=0.00..42.30 rows=930 width=132)
-> Sort (cost=2.08..2.16 rows=31 width=66)
Sort Key: rt2.id
-> Seq Scan on regtypes rt2 (cost=0.00..1.31 rows=31
width=66)
-> Hash (cost=1.04..1.04 rows=4 width=33)
-> Seq Scan on regtypedates rd2 (cost=0.00..1.04 rows=4 width=33)
---(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] pl/pgsql problem with return types
On Mar 11, 2005, at 5:54 AM, Juris Zeltins wrote: FOR P IN select pageid from pages This way you are only getting the pageid column. I think what you want is FOR P in select * from pages so that P contains the complete pages record. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] pl/pgsql problem with return types
Yep.. i have solved this problem by specifying the correct return type and variable type (should be the same) but as said, in some cases pl_exec executes with type conversion. as real example - i have : return type = SETOF new type "category_node(catid, pcatid)" variable = R, SR -> RECORD and FOR R IN select * from ... LOOP RETURN NEXT R; /* add RECORD to SETOF_of_CATEGORY_NODE */ and works... the problem seems is teh same - on altered tables there is some porblem with this :) Actualy - return type & variable in "RETURN NEXT" must be the same type. // Solved John DeSoi wrote: On Mar 11, 2005, at 5:54 AM, Juris Zeltins wrote: FOR P IN select pageid from pages This way you are only getting the pageid column. I think what you want is FOR P in select * from pages so that P contains the complete pages record. 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] [GENERAL] more execution time
ALÝ ÇELÝK wrote:
why this query needs more time? Its very slow
Difficult to say for sure - could you provide the output of EXPLAIN
ANALYSE rather than just EXPLAIN?
Some other immediate observations:
1. Perhaps don't post to so many mailing lists at once. If you reply to
this, maybe reduce it to pgsql-performance?
2. You don't say whether the row estimates are accurate in the EXPLAIN.
3. You seem to be needlessly coalescing personaldetails.masterid since
you check for it being null in your WHERE clause
4. Do you really need to cast to numeric and generate a "sorting" column
that you then don't ORDER BY?
5. Is ppid an id number? And are you sure it's safe to calculate it like
that?
6. What is balance() and how long does it take to calculate its result?
select
coalesce(personaldetails.masterid::numeric,personaldetails.id) +
(coalesce(personaldetails.id::numeric,0)/100) as sorting,
floor(coalesce(personaldetails.masterid::numeric,personaldetails.id) +
(coalesce(personaldetails.id::numeric,0)/100)) as ppid,
balance('MASTER-REGISTRATION',personaldetails.id) as balance,
balance('MASTER-REGISTRATION',pd2.id) as accbalance,
I'm guessing point 6 is actually your problem - try it without the calls
to balance() and see what that does to your timings.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Conver bool to text
Is there a way to convert a boolean value to text and/or vice versa? You can select 'true'::boolean, which *seems* to convert a text string to boolean, but select 'true'::text::boolean earns the message ERROR: cannot cast type text to boolean You can't go the other way either SELECT true::text ERROR: cannot cast type boolean to text and to_char() doesn't know about booleans. It's not that difficult to write a fuction to convert boolean to text, but I'm wondering if there's already something that does this? -- Edmund Bacon <[EMAIL PROTECTED]> ---(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] Conver bool to text
Edmund Bacon wrote:
> Is there a way to convert a boolean value to text and/or vice versa?
>
[cause it doesn't work as expected]
>
> It's not that difficult to write a fuction to convert boolean to text,
> but I'm wondering if there's already something that does this?
You can help yourself by misusing the $type_in and $type_out functions:
create or replace function bool2text(bool) returns text as $$
select textin(boolout($1));
$$ language sql;
create cast (bool as text) with function bool2text(bool);
This is somewhat generic. Since the name of the *in and *out function
can be read from pg_type, the conversion function can even be generated
automatic:
/*
warning: conversion via text representation isn't always right.
*/
create or replace function make_conversion_function(s text, d text)
returns void as
$$
declare
tin text;
tout text;
xp text;
begin
select typinput into tin from pg_catalog.pg_type where typname=d;
select typoutput into tout from pg_catalog.pg_type where typname=s;
xp:='create or replace function as_' || d || '(' || s || ') returns '
|| d || ' as $BODY$ select ' || tin || '(' || tout || '($1)) $BODY$
language sql;';
execute xp;
/* create cast analogue..*/
return;
end;
$$ language plpgsql;
select make_conversion_function('text','bool');
select make_conversion_function('bool','text');
select as_bool('true'),as_text(true);
---
Marco
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
