[SQL] Some help with functions-syntax

[Jan Meyland Andersen]

I have some problem with writing a function.

I have made this function which I can't get it to work.

This is probaly a triviel question but i'm new to plsql, so this is a
showstopper for me.

DECLARE
        _relkind char;
        _RES "EMS"."KeySet";
        _WHERECLAUSE text;
BEGIN
        SELECT pgc.relkind INTO _relkind
        FROM pg_class pgc JOIN pg_namespace pgn ON pgc.relnamespace=pgn.oid
        WHERE pgn.nspname=$1 AND pgc.relname=$2;

        IF _relkind = 'r' THEN
                _WHERECLAUSE := '(fknam.nspname = ''$1'' AND fkc.relname = 
''$2'')';
        END IF;

        IF _relkind = 'v' THEN
                _WHERECLAUSE := '';
        END IF;

        FOR _RES IN
                SELECT  fknam.nspname AS "FKTABLE_SCHEM",
                        fkc.relname AS "FKTABLE_NAME",
                        fka.attname AS "FKCOLUMN_NAME",
                        pknam.nspname AS "PKTABLE_SCHEM",
                        pkc.relname AS "PKTABLE_NAME",
                        pka.attname AS "PKCOLUMN_NAME",
                        fkcon.conname AS "FK_NAME",
                        pkcon.conname AS "PK_NAME",
                        CASE
                                WHEN pkcon.contype = 'p' THEN 'PRIMARY'
                                WHEN pkcon.contype = 'u' THEN 'UNIQUE'
                        END AS "UNIQUE_OR_PRIMARY"
                FROM    pg_constraint AS fkcon
                        JOIN pg_namespace AS fknam ON 
fkcon.connamespace=fknam.oid
                        JOIN pg_class AS fkc ON fkc.oid=fkcon.conrelid
                        JOIN pg_attribute fka ON fka.attrelid=fkc.oid AND 
fka.attnum =
ANY(fkcon.conkey)
                        JOIN pg_constraint AS pkcon ON 
fkcon.confrelid=pkcon.conrelid AND
fkcon.confkey=pkcon.conkey
                        JOIN pg_namespace pknam ON pkcon.connamespace=pknam.oid
                        JOIN pg_class pkc ON pkc.oid=fkcon.confrelid
                        JOIN pg_attribute pka ON pka.attrelid=pkc.oid AND 
pka.attnum =
some(pkcon.conkey)
                WHERE   (
                        (pkcon.conkey[1] = pka.attnum AND fkcon.conkey[1] = 
fka.attnum) OR
                        (pkcon.conkey[2] = pka.attnum AND fkcon.conkey[2] = 
fka.attnum) OR
                        (pkcon.conkey[3] = pka.attnum AND fkcon.conkey[3] = 
fka.attnum) OR
                        (pkcon.conkey[4] = pka.attnum AND fkcon.conkey[4] = 
fka.attnum) OR
                        (pkcon.conkey[5] = pka.attnum AND fkcon.conkey[5] = 
fka.attnum)
                        ) AND || _WHERECLAUSE
                LOOP
                RETURN NEXT _RES;
        END LOOP;
        RETURN;
END;

I get the following error:

ERROR: operator does not exist: || text
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You may
need to add explicit type casts.


How do I solve this?

I also have a question about how to write CASE-statement? I am only able
to use if.

Regards

Jan






---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings