From: limr@yahoo.com (robert)
Subject: Re: Escaping the $1 parameter in stored procedures
Date: 15 Oct 2003 07:12:44 -0700
Organization: http://groups.google.com
Lines: 43
Message-ID: <75f517f5.0310150612.106c5d78@posting.google.com>
References: <75f517f5.0310131218.3a2907e5@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

Found a solution:

-- my_constraint(0) = turn off constraint
-- my_constraint(1) = turn ON constraint
CREATE OR REPLACE FUNCTION my_constraint(INTEGER)
RETURNS VARCHAR
  AS '
	DECLARE
		cmd VARCHAR;
	BEGIN
	        IF $1 = 0
		THEN
			RAISE NOTICE ''Turning OFF constraints'';
			cmd := ''ALTER TABLE $tName DROP CONSTRAINT "$1"'';
			EXECUTE cmd;
			cmd := ''ALTER TABLE $tName DROP CONSTRAINT "$2"'';
			EXECUTE cmd;
		ELSE
			RAISE NOTICE ''Turning ON constraints'';
			ALTER TABLE $tName
				ADD FOREIGN KEY(key1) REFERENCES table1;
			ALTER TABLE $tName
				ADD FOREIGN KEY(key2) REFERENCES table2;
		END IF;
		RETURN ''OK'';
	END;'
	LANGUAGE plpgsql;"


limr@yahoo.com (robert) wrote in message news:<75f517f5.0310131218.3a2907e5@posting.google.com>...
> I'm running Postgres 7.3.2 in Redhat 9.0.
> 
> I'm trying to execute a function below defined as a stored procedure
> 
>    ALTER TABLE tms_schedule DROP CONSTRAINT "$1";
> 
> However, postgres thinks the "$1" is a parameter value.  How do I tell
> postgres to treat it as a literal $1?
> 
> TIA,
> Robert