Some help with functions-syntax
От | Jan Meyland Andersen |
---|---|
Тема | Some help with functions-syntax |
Дата | |
Msg-id | 1364.62.66.218.59.1169073199.squirrel@mail.agile.dk обсуждение исходный текст |
Ответы |
Re: Some help with functions-syntax
|
Список | pgsql-sql |
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; BEGINSELECT pgc.relkind INTO _relkindFROM pg_class pgc JOIN pg_namespace pgn ON pgc.relnamespace=pgn.oidWHERE pgn.nspname=$1AND 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 ASfkc 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;ENDLOOP;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
В списке pgsql-sql по дате отправления: