RedHat 6.2 /Postgres 6.53
I'm still very new at PL/PGSQL but I have looked thru all the
documentation I could find before sending this in.
I have a function that I would like to be able to apply to multiple
tables without having to duplicate the code. I would like to be able
to pass in the table name I would like to apply the function to. I
cannot seem to get this to work. Below is what I have so far. I
would like to be able to have
computers_equal(comp_one,comp_two,comp_table_one,comp_table_two);
I cannot seem to find a way to pass in a text string to be used in
the select into statements.
Anyone got any ideas on how I fix this other that writing a function
for all the permutations (ordered_parts vs used_parts , ordered_parts
vs new_parts, used_parts vs ordered_parts, new_parts vs
ordered_parts,new_parts vs used_parts, used_parts vs ordered_parts)
Any advice would be appreciated.
create table ordered_parts
(computer_number int4,part_number int4
);
create table used_parts
(computer_number int4,part_number int4
);
create tabel new_parts
(computer_number int4,part_number int4
);
insert into ordered_parts values(1,401);
insert into ordered_parts values(1,402);
insert into used_parts values(2,401);
insert into used_parts values(2,402);
insert into used_parts values(3,401);
insert into used_parts values(3,403);
insert into new_parts values(4,401);
insert into new_parts values(4,402);
insert into new_parts values(5,401);
insert into new_parts values(5,403);
CREATE FUNCTION computers_equal(int4 , int4) RETURNS bool AS 'DECLARE true CONSTANT bool DEFAULT ''t''; false
CONSTANTbool DEFAULT ''f''; parts_list RECORD;BEGIN SELECT INTO parts_list part_number,part_label from
ordered_parts where computer_number = $1 except select
part_number,part_label from used_parts where computer_number= $2; IF NOT FOUND THEN -- now test the reverse
SELECT INTO parts_list
part_number,part_label from used_parts where computer_number = $2
except select part_number,part_label from ordered_parts where
computer_number= $1; IF NOT FOUND THEN RETURN true; ELSE RETURN false; END
IF;
ELSE RETURN false; END IF;
END;
' LANGUAGE 'plpgsql';
--This will list all the comptuers that match
select distinct on computer_number computer_number from used_parts
where computers_equal(1,computer_number) and computer_number!=1;
/*
--this doesn't work
CREATE FUNCTION computers_equal(int4 , int4,text,text) RETURNS bool AS 'DECLARE true CONSTANT bool DEFAULT ''t'';
falseCONSTANT bool DEFAULT ''f''; parts_list RECORD;BEGIN SELECT INTO parts_list part_number,part_label from $3
where computer_number = $1 except select part_number,part_label from
$4 where computer_number= $2; IF NOT FOUND THEN -- now test the reverse SELECT INTO parts_list
part_number,part_label from $4 where computer_number = $2 except
select part_number,part_label from $3 where computer_number= $1; IF NOT FOUND THEN RETURN true;
ELSE RETURN false; END IF;
ELSE RETURN false; END IF;
END;
' LANGUAGE 'plpgsql';
select distinct on computer_number computer_number from used_parts
where computers_equal(1,computer_number,ordered_parts,used_parts) and
computer_number!=1;
*/
--
______________________________________________________________________
Dirk Elmendorf, CTE Main: 210-892-4000
Rackspace Managed Hosting
Weston Center Fax: 210-892-4329
112 East Pecan, Suite 600 Email:dirk@rackspace.com
San Antonio, TX 78205 <http://www.rackspace.com>
