Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    9

    Question Unanswered: Can't adapt pgplsql function for v8.1

    Hello everyone ,

    Some time ago with some suffering I made a pgplsql function for the v8.4 to reserve auto-generated ids before some massive but unfortunately "id fixed" transactions and it works just fine. Today I've tried to execute it in v8.1 and I get an error saying:
    ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT in or close to «QUERY»
    Just to clarify: yes, I'm actually trying to downgrade the compatibility to version 8.1 since using any more recent version is not an option in my case.

    The function itself:
    Code:
    CREATE OR REPLACE FUNCTION schema1.reserve_medcomprado(in_regcount integer, in_tempname character varying)
      RETURNS SETOF integer AS
    $BODY$
    BEGIN
    	SET SEARCH_PATH TO 'schema1';
    
    	EXECUTE 'CREATE TABLE ' || QUOTE_IDENT(in_tempname) || ' (idmedcomprado integer NOT NULL) WITH (OIDS=FALSE)';
    
    	FOR i IN 1..in_regcount LOOP
    		EXECUTE 'INSERT INTO ' || QUOTE_IDENT(in_tempname) || $q$ VALUES(nextval('schema1.medcomprado_idmedcomprado_seq'))$q$;
    	END LOOP;
    	
    	RETURN QUERY EXECUTE 'SELECT * FROM ' || QUOTE_IDENT(in_tempname);
    
    	EXECUTE 'DROP TABLE IF EXISTS ' || QUOTE_IDENT(in_tempname);
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100
      ROWS 1000;
    I'm pretty new to pgplsql so I've got no ideas how to correct the issue and make the function more "compatible".

    Any ideas?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    8.1 has been de-supported for several years now. The oldest supported is 8.4 (and that will be de-supported next year).
    You simply cannot use a feature in a version where this feature isn't available.

    Besides: your function does not return a set anyways. Why would you need that (actually your function as you have shown it to use doesn't return anything
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Apr 2013
    Posts
    9
    v8.1 is a temporal solution I need only for now, but it has to be v8.1 none the less since I do not control the target environment which is obviously messed up. Actually I have no idea what brainy put v8.1 there... And yes, I know it's out of support as well as v8.4 soon will be, so I will migrate the db to 9.2 or something later.

    The script I posted does actually return a set of integers as per RETURN statement:
    Code:
    RETURN QUERY EXECUTE 'SELECT * FROM ' || QUOTE_IDENT(in_tempname);
    ---------------------
    Update:
    I've just modified the script to:
    Code:
    CREATE OR REPLACE FUNCTION schema1.reserve_medcomprado(in_regcount integer)
      RETURNS SETOF integer AS
    $BODY$
    BEGIN
    	FOR i IN 1..in_regcount LOOP
    		RETURN NEXT nextval('schema1.medcomprado_idmedcomprado_seq');
    	END LOOP;
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE;
    and it seems to work in v8.4. Though I have no idea if it'd work in v8.1...
    Last edited by akaine; 04-15-13 at 05:44.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •