Forums PostgreSQL.fr

cvs -d :ext:gildas@cvs.pgfoundry.org:/cvsroot/npgtools checkout npgtools
Cannot access /cvsroot/npgtools/CVSROOT
No such file or directory

CREATE TABLE versions
(
  id_version character varying(16) NOT NULL,
  commands character varying,
  "number" integer,
  CONSTRAINT versions_pkey PRIMARY KEY (id_version)
)
WITH (OIDS=FALSE);

INSERT INTO versions(id_version, commands, "number") VALUES ('1.0.3211', '', 3211);

---------------------------------------------------------
---                DROP PREVIOUS PATCH                ---
---------------------------------------------------------
DROP FUNCTION IF EXISTS PatchDatabase();

---------------------------------------------------------
---                   PREPARE PATCH                   ---
---------------------------------------------------------
CREATE FUNCTION PatchDatabase() RETURNS void AS
$$
DECLARE
  record RECORD;
  recordVersion RECORD;
  
  BUILD_VERSION integer :=      3321;
  FULL_VERSION  varchar := '1.0.3321';
  
  BEGIN	
    SELECT * INTO record FROM versions WHERE "number">=BUILD_VERSION;
    IF record IS NULL THEN
      BEGIN
	    CREATE OR REPLACE FUNCTION my_function(channelid integer)
          RETURNS SETOF record AS
        $BODY$
        DECLARE
            chain_key INTEGER;
            chain_id INTEGER;
            record RECORD;
        BEGIN
            FOR chain_id IN SELECT id_acq_chain FROM channels, acq_chains WHERE channels.key_acq_chain = acq_chains.key_acq_chain and id_channel=$1 ORDER BY channels.date_modif, acq_chains.date_modif DESC LIMIT 1
            LOOP
                SELECT * INTO record FROM acq_chains WHERE id_acq_chain=chain_id ORDER BY date_modif DESC LIMIT 1;
                RETURN NEXT record;
           END LOOP;
            RETURN;
        END
        $BODY$
          LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER COST 100 ROWS 1;

	    RETURN;
	    EXCEPTION WHEN unique_violation THEN
	  END;
    END IF;
  END;
$$
LANGUAGE 'plpgsql';

---------------------------------------------------------
---                   LAUNCH PATCH                    ---
---------------------------------------------------------
SELECT * FROM PatchDatabase();

ERROR: syntax error at or near "$1"
État SQL :42601
Contexte : SQL statement in PL/PgSQL function "patchdatabase" near line 28