Re: plpgsql question

[Matthew Peter <survivedsushi@xxxxxxxxx>]

snip

WHERE my_tbl_id = $1
AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username END

or

WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)

or

WHERE my_tbl_id = $1 AND COALESCE($2, username) = username

With predicates such as these you wouldn't need to use EXECUTE and
you could write the query only once.

That did work. Thanks.

One other quick question, (figure it still applies to the subject line :) when returning a row from a function I'm trying to include an aggregate, but it's not showing up in the query result and I think it's because it's not included in the RETURN NEXT row;?  How do I return it as part of the resultset...

create or replace function getrecord(int,text) RETURNS SETOF my_tbl
 as $$
DECLARE
 row my_tbl%rowtype;

BEGIN
FOR row IN  SELECT *, SUBSTR(title,1,25) as short_title FROM my_tbl
WHERE ...
LOOP
RETURN NEXT row;
END LOOP;

RETURN;

END;
$$ LANGUAGE plpgsql;

Thanks

---

Yahoo! Photos
 
Got holiday prints? See all the ways to get quality prints in your hands ASAP.