Hello,
Well right now I'm working with PL/pgsql...
I have also faced up to the problem of returning more than one value from
a function, and an array would be the ideal thing for that purpose, but as
long as I wasn't able to find a way of returning or declaring arrays, I played
a kind of trick concatenateing all the values I needed in a Text and filtering
them in the function that receives them.
I mean...I split the String up using some of the support functions of
Postgres (trim(), substr(), ...)
Well, this solution has worked, but I still wonder if there is a better way
of doing it.
Moreover, I have also a big problem with 'dynamic queries'. Basically I try
to generate a query to add a new column to a table...
Fist of all I have to check if the table that I want to alter exists and if
it is not inherited by other tables, this is what I use PL/pgSQL for, but after
checking that everything is allright when I try to generate the query it doesn't
work.
This is more or less what I'm trying to do...
CREATE FUNCTION addColumn(oid, TEXT, TEXT) RETURNS text AS '
DECLARE
-- Alias for the parameters
name_atrib ALIAS FOR $2;
name_type ALIAS FOR $3;
name_table text;
tuples RECORD;
BEGIN
-- I check if the OID belongs to a table
nomb_tabla := oidToText($1);
IF name_table = NULL THEN
RAISE EXCEPTION '' The OID % doesn't belong to any table!!!'', $1;
ELSE
-- I check whether the table is inherited or not.
SELECT INTO tuplas * FROM pg_inherits WHERE inhparent = $1;
IF NOT FOUND THEN
RAISE NOTICE ''This table can be altered'';
EXECUTE ''ALTER TABLE ''||name_table
|| '' ADD COLUMN '' || name_atrib || name_type;
END IF;
END IF;
END;
' LANGUAGE 'plpgsql';
I have tried using PERFOM instead of EXECUTE and also using the functions quote_indent(text)
and quote_literal(text), but they are not even defined in the list of functions.
Test=# \df quote_literal
List of functions
Result | Function | Arguments
--------+----------+-----------
(0 rows)
Test=# \df quote_ident
List of functions
Result | Function | Arguments
--------+----------+-----------
(0 rows)
Up to now, I don't now anything else to do :-(
I look forward to receiving an answer in order to go on working on this.
Sory for such a large, boring (and probably not very clear) message.
Stay Safe & Happy,
:* TankGirl
