We are in the process of migrating from PostgreSQL v9.4 to v13. In doing so we have to migrate all our functions too, The following FUNCTION issue a 42704 error stating that record_type cannot be found in either prod or public. This function worked in v9.4 but when I copy the FUNCTION sql below and input it in Pgadmin4 in v13 to duplicate the function, I receive ERROR 42704. A Visual Basic .NET application calls this function so I prefer to tweak it only enough so it loads in v13 and works in a similar manner.
This function takes three arguments tablename - the name of the table being queried selectcols - a comma-separated list of the cols to be selected clause - a spatial clause to query the table spatially
As a novice to postgresql FUNCTIONS, I believe the issue is the variable record_type as it's not used elsewhere in the function. I have seen the following syntax:
SELECT * INTO record_type FROM tablename WERE clause
The function below does not employ that syntax and I was wondering if that's a possible way to fix this. Additionally, I have read to avoid using SETOF as this is obsolete syntax.
Just looking for a suggestion/example of how to rectify the following:
CREATE OR REPLACE FUNCTION prod._fsearchaa(
tablename character varying DEFAULT::NULL character varying,
selectcols text DEFAULT NULL::text,
clause text DEFAULT NULL::text)
RETURNS SETOF record_type AS
$BODY$
DECLARE SQL text DEFAULT NULL;
r record;
BEGIN
SQL := ' SELECT ' || selectcols || ' FROM ' || tablename || ' WHERE ' || clause AS sql;
FOR r in EXECUTE sql LOOP
RETURN NEXT r;
END LOOP;
END$BODY$
LANGUAGE plpgsql STABLE;
COST 100;
ROWS 1000;
ALTER FUNCTION prod.fsearchaa(character varying, text, text) SET search_path=prod,public;
ALTER FUNCTION prod.fsearchaa(character varying, text, text)
OWNER TO dbuser;
I modified the above to be and this appears to work without specifying SETOF record_type.I'm still curious if anyone would have additional input. The modified proc is as follows:
Col1,Co12,Col3 are found in the table that is passed in as tablename. Hard coding the column names makes the proc more rigid. I like the idea of passing in selectcols which would equate to "column_id_1, column_id_2, column_id_3". Not sure if I could split the selectcols with the "." delimiter and add that to the RETURN TABLE clause near the beginning of the FUNCTION.
CHANGE:
I am still focuses on getting this proc to work with any input tablename and any set of corresponding columns associated with that table coupled with a spatial string.
I attemped to use the anyelement clause for the input tablename. The reason being is that I keep receiving an error on the SETOF <variable_name> AS shown above in the original code. The changes below allowed me to at least save the function in the schema Functions repository. However when i extecute the function as follows:
SELECT * FROM prod._fsearchaa(<tablename>, <columns>, <spatial_clause>)
**I receive the ERROR: could determine polymorphic type because input has type unknown (42804). The function I am now test with the polymorphic dynamic argument is as follows. I have highlighted all the places I use anyelement in the function below.
CREATE OR REPLACE FUNCTION prod._fsearchaa(
tablename **anyelement**,
selectcols text DEFAULT NULL::text,
clause text DEFAULT NULL::text)
RETURNS SETOF **anyelement** AS
$BODY$
DECLARE SQL text DEFAULT NULL;
r record;
BEGIN
SQL := ' SELECT ' || selectcols || ' FROM ' || tablename || ' WHERE ' || clause AS sql;
FOR r in EXECUTE sql LOOP
RETURN NEXT r;
END LOOP;
END$BODY$
LANGUAGE plpgsql STABLE;
COST 100;
ROWS 1000;
ALTER FUNCTION prod.fsearchaa(**anyelement**, text, text) SET search_path=prod,public;
ALTER FUNCTION prod.fsearchaa(**anyelement**, text, text)
OWNER TO dbuser;
