From: Jan Meyland Andersen <jma@agile.dk>
To: pgsql-sql@postgresql.org
Subject: Some help with functions-syntax
Date: Wed, 17 Jan 2007 23:33:19 +0100 (CET)
Message-ID: <1364.62.66.218.59.1169073199.squirrel@mail.agile.dk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jma@agile.dk
Subject: Re: Some help with functions-syntax
In-Reply-To: <1364.62.66.218.59.1169073199.squirrel@mail.agile.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox