Обсуждение: How do I insert a record into a table?

I want to write a query like:

INSERT INTO table SELECT func(args);

where func is defined as:

CREATE OR REPLACE FUNCTION func(args)
RETURNS table
AS $_$
    ...
$_$ LANGUAGE plpgsql;

Unfortunately, when I try to do this, I get:

ERROR: column "first_column" is of type integer but expression is of
type record

I get this error even if I list the columns:
INSTER INTO table(first_column, second_column, ...) ....

So how do I take a record returned from a function, and insert it as a
row into a table?

Brian

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);

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.

Michael Glaesemann
grzm seespotcode net

--- Brian Hurt <bhurt@janestcapital.com> wrote:

>
> I want to write a query like:
> INSERT INTO table SELECT func(args);
> where func is defined as:
> CREATE OR REPLACE FUNCTION func(args)
> RETURNS table
> AS $_$
>     ...
> $_$ LANGUAGE plpgsql;
> Unfortunately, when I try to do this, I get:
> ERROR: column "first_column" is of type integer but expression is of
> type record
> I get this error even if I list the columns:
> INSTER INTO table(first_column, second_column, ...) ....
> So how do I take a record returned from a function, and insert it as a
> row into a table?

What does a select * from table; look like?
What does a select * from func(args); look like?

Regards,
Richard Broersma Jr.

Brian Hurt wrote:

>
> I want to write a query like:
>
> INSERT INTO table SELECT func(args);
>
> where func is defined as:
>
> CREATE OR REPLACE FUNCTION func(args)
> RETURNS table
> AS $_$
>    ...
> $_$ LANGUAGE plpgsql;
>
> Unfortunately, when I try to do this, I get:
>
> ERROR: column "first_column" is of type integer but expression is of
> type record
>

If I understand what you are trying to do then one suggestion would be
to execute everything inside the function.
    SELECT * FROM my_function(args); --(args is an array that looks like
this:  '{tablename,column1,column2,...}'
then:
CREATE OR REPLACE FUNCTION my_function(_varchar)
RETURNS int4 AS
$BODY$
DECLARE
variables alias for $1;

BEGIN

EXECUTE 'INSERT INTO'||variables[0]||'
VALUES('||variables[i]||','||variables[2]||', '|| ... ||')';

RETURN 1;
END
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

--- Brian Hurt <bhurt@janestcapital.com> wrote:
 

I want to write a query like:
INSERT INTO table SELECT func(args);
where func is defined as:
CREATE OR REPLACE FUNCTION func(args)
RETURNS table
AS $_$   ...
$_$ LANGUAGE plpgsql;
Unfortunately, when I try to do this, I get:
ERROR: column "first_column" is of type integer but expression is of 
type record
I get this error even if I list the columns:
INSTER INTO table(first_column, second_column, ...) ....
So how do I take a record returned from a function, and insert it as a 
row into a table?   

What does a select * from table; look like?
What does a select * from func(args); look like?
 

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

On Jun 1, 2007, at 14:54 , Brian Hurt wrote:

> 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.

If you'd like, you could throw in a RAISE NOTICE (or other level) so
you get some other information.

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann wrote:

>
> On Jun 1, 2007, at 14:54 , Brian Hurt wrote:
>
>> 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.
>
>
> If you'd like, you could throw in a RAISE NOTICE (or other level) so
> you get some other information.


If I just do an insert into the table, I see:
bhurt_dev=# INSERT INTO test1(id, name) VALUES (1, 'foo');
INSERT 0 1
bhurt_dev=#

But if I define:
CREATE FUNCTION insert_test1(p_id INT, p_name VARCHAR) RETURNS VOID
AS $_$
BEGIN
    INSERT INTO test1(id, name) VALUES(p_id, p_name);
END
$_$ LANGUAGE plpgsql;

CREATE VIEW view1 AS SELECT * FROM test1;

CREATE RULE rule1 AS ON INSERT TO view1 DO INSTEAD SELECT
insert_test1(NEW.id, NEW.name);

and then do:
bhurt_dev=# INSERT INTO view1(id, name) VALUES (2, 'bar');
 insert_test1
--------------

(1 row)

bhurt_dev=#

See the difference?

It's stylistic, and doesn't actually change anything.

Brian

On Jun 1, 2007, at 15:20 , Brian Hurt wrote:

> See the difference?

Yes, I saw/see the difference. I was trying to suggest a way for you
to get additional information. As you're doing a SELECT rather than
an INSERT, of course the server response is going to be different.
Just trying to give you options :)

Michael Glaesemann
grzm seespotcode net