Thread: Some help with functions-syntax

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

Take a look at the documentation and examples again:

http://www.postgresql.org/docs/8.2/interactive/plpgsql-control- 
structures.html#PLPGSQL-RECORDS-ITERATING

There are two forms to iterate over the query:

1. FOR target IN query LOOP
2. FOR target IN EXECUTE text_expression LOOP

In your code you have mixed the two together. You appear to be trying  
to concatenate a string on to the end of a query expression. My  
suggestion is to eliminate the string you created (_WHERECLAUSE) and  
add the proper conditions to the query expression you already have.




On Jan 17, 2007, at 5:33 PM, Jan Meyland Andersen wrote:

> 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.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

> There are two forms to iterate over the query:
>
> 1. FOR target IN query LOOP
> 2. FOR target IN EXECUTE text_expression LOOP

Thanks for your answer.

> My suggestion is to eliminate the string you created (_WHERECLAUSE) and
> add the proper conditions to the query expression you already have.

But the problem here is that the where-clause depends on the relkind. That
is why I'm trying to solve the problem this way.

How do I then write EXECUTE queries on multiple lines, if I go with this
solution?

Regards

Jan

On Jan 18, 2007, at 2:38 AM, Jan Meyland Andersen wrote:

> But the problem here is that the where-clause depends on the  
> relkind. That
> is why I'm trying to solve the problem this way.

I think that clause can be written as a subselect and added to the  
expression. Or just make that a separate function and AND the  
function call with your query expression.


>
> How do I then write EXECUTE queries on multiple lines, if I go with  
> this
> solution?

You can make it a big multi-line string. Or you can build the string,  
something like:

declare
_sql

begin

_sql := _sql + 'select ...'
_sql := _sql + ' where ...'


But using the form without execute is much better. PostgreSQL will  
only have to plan the query the first time it is called. Using  
EXECUTE is much less efficient.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL