howzit Henry
I've pasted in the head of one of my functions. Hope it helps. It is
called through the following:
SELECT * from fn_usr32_show_sections ( <integer> );
Rory
On 07/09/04, Henry Combrinck (henry@metroweb.co.za) wrote:
> Thanks for the CREATE TYPE samples. *Using* the custom types seems to be
> a problem (or rather, I'm using it incorrectly). The following code fails
> with the error message
>
> "WARNING: Error occurred while executing PL/pgSQL function f_test00
> WARNING: line 8 at SQL statement
> ERROR: Attribute "rec1" not found"
>
> /* create same columns as table1 */
> create type TYPE_T1 as (col1 int, col2 int,... etc);
...
> declare
> rec1 TYPE_T1%ROWTYPE;
> begin
> for rec1 in select * from table1
> loop
> select f_test01(rec1); /* this is where it fails. */
> end loop;
> return 0;
> end;
> ' LANGUAGE 'plpgsql';
CREATE TYPE sec_sections as (
sectionid INTEGER,
sectionname VARCHAR,
secupdated VARCHAR,
secreports INTEGER,
secusers INTEGER
);
CREATE OR REPLACE FUNCTION
fn_usr32_show_sections ( integer ) RETURNS setof sec_sections
AS '
DECLARE
userid ALIAS for $1;
resulter sec_sections%rowtype;
BEGIN
IF userid is NULL THEN
RAISE EXCEPTION ''No user provided : ref usr32'';
RETURN 0;
END IF;
PERFORM fn_util07_isadmin(userid);
FOR resulter IN
SELECT
s.n_id as sectionid,
s.t_name as sectionname,
COALESCE(to_char(lupd.updated, ''DD Mon''), ''None'') as secupdated,
COALESCE(rept.num,0) as secreports,
COALESCE(usr.num,0) as secusers
...etc..
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
