On 12/05/2012 04:58 PM, Tom Lane wrote:
Henrik Kuhn <henrik.kuhn@xxxxxxxxxxx> writes:
On 12/05/2012 04:07 PM, Tom Lane wrote:
Henrik Kuhn <henrik.kuhn@xxxxxxxxxxx> 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
begin:vcard
fn:Henrik Kuhn
n:Kuhn;Henrik
org:origenis GmbH
adr:;;Am Klopferspitz 19a;Martinsried;Bavaria;82152;Germany
email;internet:henrik.kuhn@xxxxxxxxxxx
title:Dr.
tel;work:+49 89 7801676-210
tel;fax:+49 89 7801676-777
x-mozilla-html:TRUE
url:http://www.origenis.de
version:2.1
end:vcard
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
