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
