Thread: CREATE RULE fails with 'ERROR: SELECT rule's target list has too many entries'
CREATE RULE fails with 'ERROR: SELECT rule's target list has too many entries'
From
Henrik Kuhn
Date:
Hi,
can somebody give me some insights why the creation of this rule fails
with 'ERROR: SELECT rule's target list has too many entries'?
CREATE RULE "_RETURN" AS
ON SELECT TO history_relation
DO INSTEAD
SELECT
history_relation.id,
history_relation.relname,
pg_attribute.attname AS keyname
FROM
history_relation, pg_index, pg_class, pg_attribute
WHERE
pg_class.oid = history_relation.relname::regclass AND
indrelid = pg_class.oid AND
pg_attribute.attrelid = pg_class.oid AND
pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary;
The purpose of this rule is to retrieve the primary key name upon the
given table name (relname) stored in the table 'history_relation' upon
SELECT only.
Kind regards,
Henrik
Re: CREATE RULE fails with 'ERROR: SELECT rule's target list has too many entries'
From
Tom Lane
Date:
Henrik Kuhn <henrik.kuhn@origenis.de> writes:
> can somebody give me some insights why the creation of this rule fails
> with 'ERROR: SELECT rule's target list has too many entries'?
Probably that history_relation has fewer than three columns? But if
you want something more than guessing, you'd have to provide more
context, such as the definitions of the underlying tables. Why are
you trying to execute this command anyway?
regards, tom lane
Re: CREATE RULE fails with 'ERROR: SELECT rule's target list has too many entries'
From
Henrik Kuhn
Date:
On 12/05/2012 04:07 PM, Tom Lane wrote:
> Henrik Kuhn <henrik.kuhn@origenis.de> writes:
>> can somebody give me some insights why the creation of this rule fails
>> with 'ERROR: SELECT rule's target list has too many entries'?
> Probably that history_relation has fewer than three columns? But if
> you want something more than guessing, you'd have to provide more
> context, such as the definitions of the underlying tables. Why are
> you trying to execute this command anyway?
>
> regards, tom lane
The history_relatio-DDL is quite simple:
CREATE TABLE history_relation (
id UUID NOT NULL DEFAULT uuid_generate_v4()
, relname name NOT NULL CHECK ( relname::regclass IS NOT NULL
) -- use regclass to validate
, PRIMARY KEY (id)
);
But are your really sure, that the error is thrown just because of there
are not more than 3 cols in history_relation? And then why is the error
text '... too many entries' ? It sounds more that the RULE parser can
not handle the join over four tables?
The purpose is:
I want to set up an own polymorphic foreign key reference validation
system. The key point to this is using uuid as prim. key over all tables
to reference to. Because the corresponding prim key names may be
different I need to query for its name to build the corresponding join
stmt. The TABLE history_relation is for keeping track of the foreign tables.
Regards,
Henrik
Re: CREATE RULE fails with 'ERROR: SELECT rule's target list has too many entries'
From
Tom Lane
Date:
Henrik Kuhn <henrik.kuhn@origenis.de> writes:
> On 12/05/2012 04:07 PM, Tom Lane wrote:
>> Henrik Kuhn <henrik.kuhn@origenis.de> writes:
>>> can somebody give me some insights why the creation of this rule fails
>> > with 'ERROR: SELECT rule's target list has too many entries'?
>> Probably that history_relation has fewer than three columns? But if
>> you want something more than guessing, you'd have to provide more
>> context, such as the definitions of the underlying tables. Why are
>> you trying to execute this command anyway?
> The history_relatio-DDL is quite simple:
> CREATE TABLE history_relation (
> id UUID NOT NULL DEFAULT uuid_generate_v4()
> , relname name NOT NULL CHECK ( relname::regclass IS NOT NULL
> ) -- use regclass to validate
> , PRIMARY KEY (id)
> );
> But are your really sure, that the error is thrown just because of there
> are not more than 3 cols in history_relation?
Yes. I think you misunderstand what that command is for: it's going to
convert the table into a view, and as a safety check it is not allowed
for the resulting view to have a different column set than the table
did.
I think what you need to do is create the underlying table with some
other name, create history_relation as a plain view, and then perhaps
you want some INSTEAD OF triggers on the view to convert insertions
etc into updates of the underlying table.
regards, tom lane
Re: CREATE RULE fails with 'ERROR: SELECT rule's target list has too many entries'
From
Henrik Kuhn
Date:
On 12/05/2012 04:58 PM, Tom Lane wrote: > Henrik Kuhn <henrik.kuhn@origenis.de> writes: >> On 12/05/2012 04:07 PM, Tom Lane wrote: >>> Henrik Kuhn <henrik.kuhn@origenis.de> writes: >>>> can somebody give me some insights why the creation of this rule fails >>>> with 'ERROR: SELECT rule's target list has too many entries'? >>> Probably that history_relation has fewer than three columns? But if >>> you want something more than guessing, you'd have to provide more >>> context, such as the definitions of the underlying tables. Why are >>> you trying to execute this command anyway? >> But are your really sure, that the error is thrown just because of there >> are not more than 3 cols in history_relation? > Yes. I think you misunderstand what that command is for: it's going to > convert the table into a view, and as a safety check it is not allowed > for the resulting view to have a different column set than the table > did. Tom, OK, I've tried a the same cretae rule stmt but without the keyname col. And it did not complain about to many entries. But now the parser complains again with: ERROR: could not convert table "history_relation" to a view because it has triggers HINT: In particular, the table cannot be involved in any foreign key relationships. The intention why I thought it is a nice feature to use a rule was, that I do not need to create an extra view object upon the table. I thought that the rule system does transform the simple select on table history_relation to my desired multi table join retrieving the extra col. keyname on the fly. Even the docs (http://www.postgresql.org/docs/9.1/static/sql-createrule.html) say: '... Thus, an ON SELECT rule effectively turns the table into a view, whose visible contents are the rows returned by the rule's SELECT command rather than whatever had been stored in the table (if anything). ...'. No word upon that the returning col. set/count must be the same > I think what you need to do is create the underlying table with some > other name, create history_relation as a plain view, and then perhaps > you want some INSTEAD OF triggers on the view to convert insertions > etc into updates of the underlying table. > > regards, tom lane > Of course I had a view before, but a RULE seems to be much smarter in the way that it masks the underlying table e.g. a select upon a table behaves like a view, but is not defined as one. I'm switching back to the view based design. Thanks for your help Tom. Regads, Henrik