Mime-Version: 1.0
Message-Id: <p04310104b5bf2d8e41a1@[64.39.0.183]>
Date: Tue, 15 Aug 2000 12:39:29 -0500
To: pgsql-sql@postgresql.org
From: Dirk Elmendorf <delmendo@rackspace.com>
Subject: PL/PGSQL Function problem.
Content-Type: text/plain; charset="us-ascii" ; format="flowed"

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 CONSTANT bool 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'';
		false CONSTANT 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>