FAQ
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

Search Discussions

  • Michael Glaesemann at Jun 1, 2007 at 7:04 pm

    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 at Jun 1, 2007 at 7:54 pm
    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
  • Michael Glaesemann at Jun 1, 2007 at 8:01 pm

    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
  • Brian Hurt at Jun 1, 2007 at 8:20 pm
    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
  • Michael Glaesemann at Jun 1, 2007 at 8:54 pm

    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
  • Richard Broersma Jr at Jun 1, 2007 at 7:06 pm

    --- 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
    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 at Jun 1, 2007 at 7:55 pm

    Richard Broersma Jr wrote:
    --- 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
    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?

    The same. Note that I defined func to return type table.
  • Derrick Betts at Jun 1, 2007 at 7:30 pm

    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;

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 1, '07 at 6:31p
activeJun 1, '07 at 8:54p
posts9
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase