Michael Glaesemann wrote:
>
> On Jun 1, 2007, at 13:31 , Brian Hurt wrote:
>
>>
>> I want to write a query like:
>>
>> INSERT INTO table SELECT func(args);
>
>
> I think you might want to try something along the lines of
> INSERT INTO table (col1, col2, col3)
> SELECT col1, col2, col3
> FROM func(args);
What I'm really trying to do is to write a rule of the form:
CREATE OR REPLACE VIEW table AS SELECT * FROM real_table;
CREATE OR REPLACE RULE myrule AS ON INSERT TO table DO INSTEAD INSERT
INTO real_table VALUES (func(NEW));
Basically to require all inserts to be "cleaned" by func. The reason
for this is we're trying to keep a modification history of the table.
Insert is the simple case- the update and delete rules will be much more
interesting.
>
> Then again, you could wrap the whole insert into the function:
>
> CREATE FUNCTION func(args)
> RETURNS VOID
> LANGUAGE plpgsql AS $_$
> -- ...
> INSERT INTO table (col1, col2, col3)...
> $_$;
>
> then SELECT func(args); to call the function.
>
This is the current solution I'm going with. The main problem I have
with this is stylistic- it changes the result psql displays from an
insert response to a select response.
Brian