pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSome help with functions-syntax
4+ messages / 2 participants
[nested] [flat]
* Some help with functions-syntax
@ 2007-01-17 22:33 Jan Meyland Andersen <jma@agile.dk>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Meyland Andersen @ 2007-01-17 22:33 UTC (permalink / raw)
To: 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;
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Some help with functions-syntax
@ 2007-01-18 00:50 John DeSoi <desoi@pgedit.com>
parent: Jan Meyland Andersen <jma@agile.dk>
0 siblings, 1 reply; 4+ messages in thread
From: John DeSoi @ 2007-01-18 00:50 UTC (permalink / raw)
To: Jan Meyland Andersen <jma@agile.dk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Some help with functions-syntax
@ 2007-01-18 07:38 Jan Meyland Andersen <jma@agile.dk>
parent: John DeSoi <desoi@pgedit.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Meyland Andersen @ 2007-01-18 07:38 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: Jan Meyland Andersen <jma@agile.dk>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Some help with functions-syntax
@ 2007-01-18 13:47 John DeSoi <desoi@pgedit.com>
parent: Jan Meyland Andersen <jma@agile.dk>
0 siblings, 0 replies; 4+ messages in thread
From: John DeSoi @ 2007-01-18 13:47 UTC (permalink / raw)
To: Jan Meyland Andersen <jma@agile.dk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-01-18 13:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-17 22:33 Some help with functions-syntax Jan Meyland Andersen <jma@agile.dk>
2007-01-18 00:50 ` John DeSoi <desoi@pgedit.com>
2007-01-18 07:38 ` Jan Meyland Andersen <jma@agile.dk>
2007-01-18 13:47 ` John DeSoi <desoi@pgedit.com>
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