Hi all.

I've searched for postgresql and read the manual till I'm crosseyed... and no joy..

Basically... can you create variable SQL functions?

In my php I want to call some sql functions.

I can do that by:

 $sSQL = "retire('filename', 'idname', idvalue);";
ExecuteQuery($sSQL);

where filename and idname are text / varchar and idvalue is int4.

My problem is in the defintion.
Is it possible to create SQL/ postgresql functions that use variables?

ie

CREATE FUNCTION retire(text, text, int4) RETURNS int4 AS'
BEGIN
UPDATE $1 set "Retired" = 'true' where $2 = $3;
SELECT (something for return value);
END;'
LANGUAGE 'SQL'; 

(or 'plpgsql')
This comes back with:
parse error at or near "true"

I've created functions before... but they only seem to work if I define the filename and idname.
ie

CREATE FUNCTION retire(int4) RETURNS int4 AS'
BEGIN
UPDATE "FileName" set "Retired" = 'true' where "IDName" = $1;
SELECT (something for return value);
END;'
LANGUAGE 'SQL' (or 'plpgsql');

But I want to call the same function for different files!!

any ideas or pointers??

Thanks
Beth

    First off, quoting doubles for each nested string, so 'true' needs two single quotes.

    For dynamic sql, you concatenate the query string and use EXECUTE to perform the query. You will need to use pl/pgsql rather than sql.

    Man page on dynamic queries in functions

      Thanks for your reply :-)
      the double quotes didn't help... single works from the command line and works within the other functions I've created.... but was worth a shot.

      As for the EXECUTE... I've seen it, read it, printed it.. and would really appreciate an example .. the one in the manual doesn't subsitute filenames infact I can't see it using the values passed in. (Please correct me if wrong).

      I spent a frustrating afternoon yesterday where Execute lead to Perform and Prepare (and that was labeled postgresql 7.4) and I couldn't make anything work!

      Any advice would be appreciated :-)
      Beth

        I don't understand your use of filename - in postgres, you are updating tables. Also, avoid using quotations around your relationsa and use only single quotes in function definitions.

        Here's the update portion. All quotes are single.

        CREATE FUNCTION temp(text,text,int4) RETURNS integer AS '
        DECLARE
        update_table ALIAS FOR $1;
        update_field ALIAS FOR $2;
        update_id ALIAS FOR $3;
        BEGIN
        EXECUTE ''UPDATE ''|| quote_ident(update_table) || '' SET retired = ''''true'''' WHERE '' || quote_ident(update_field) || '' = '' || quote_literal(update_id);
        -- The select portion to return integer goes here
        END;
        ' language 'plpgsql';

          Thanks for that.. the create worked.. now I just have to play around with calling it etc. :-)

          And Yes in postgresql its a table not a file... some habits die hard I'm afraid...

          Beth

            7 days later

            I got the update function working...
            You can call them as follows:

            $sql = "select temp('tablename', 'fieldname', fieldvalue);";
            ExecuteQuery($sql);
            

            Now I want to create a function that deletes

            again I figure its pretty simple... but I must be missing a quote?? or something ...

            CREATE FUNCTION delete(text,text,int4) RETURNS integer AS '
            DECLARE 
            tablename ALIAS FOR $1; 
            fieldname ALIAS FOR $2; 
            fieldvalue ALIAS FOR $3; 
            BEGIN 
            EXECUTE ''DELETE FROM ''|| quote_ident(tablename) || '' WHERE '' || quote_ident(fieldname) || ''  =  '' || quote_literal(fieldvalue); 
            RETURN fieldvalue;
            END;
            ' language 'plpgsql';
            

            which creates... but when I call it

            select delete('table', 'field', 5);
            

            it just returns
            NOTICE: Error occurred while executing PL/pgSQL function delete
            NOTICE: line 6 at execute statement
            ERROR: parser: parse error at or near "DELETE"
            and I've been playing with the quotes... and can't see what I've done wrong... any help appreciated!!!
            :-)
            Beth

              delete is not a suitable name for a function.

                DELETE is a reserved word, which would explain an error, but not the one you are getting.

                I assume that you are not showing the actual command, as 'table' is a reserved word and will give an error. Anyway, I can create the function as you have posted, so the quoting is correct.

                web=# select delete('fincandidate','candidate_id',16);

                delete

                16
                (1 row)

                web=# select delete('table','candidate_id',16);
                NOTICE: Error occurred while executing PL/pgSQL function delete
                NOTICE: line 6 at execute statement
                ERROR: parser: parse error at or near "table"

                  feeling pretty silly...
                  but the (not seeing the) 'forest for the trees' syndrome is common right?
                  Thanks again :-)
                  Beth

                  PS... yes 'table' and 'field' were just egs as my actual names are long and ugly like: 'User_Maintenance'

                    Write a Reply...