0

With reference solution I've posted in my previous post resulted in one more situation. While trying to insert into my destination table(schema as below).

-- Table: normalized_transaction

-- DROP TABLE normalized_transaction;

CREATE TABLE normalized_transaction
(
  transaction_id uuid,
  file_id uuid,
  account_number character varying(40),
  currency character varying(3),
  trade_date date,
  value_date date,
  narration character varying(200),
  amount numeric,
  mesitis_account_number character varying(50),
  tag character varying(255),
  supporting_file_id uuid,
  supporting_record_id uuid,
  status integer DEFAULT 0,
  source_type integer,
  record_index integer DEFAULT 0
)

with using a query like

INSERT INTO normalized_transaction(account_number, currency, trade_date)
select gen_Test('english');
fetch all in english;

Result into error:

ERROR:  INSERT has more target columns than expressions
LINE 2: ...NSERT INTO normalized_transaction(account_number, currency, ...
                                                             ^
********** Error **********

ERROR: INSERT has more target columns than expressions
SQL state: 42601
Character: 53



select gen_Test('english');
    fetch all in english;

Just for Reference Output of above Query:

What is appropriate way to insert the result from this into table.

Community
  • 1
  • 1
Harsimranjeet Singh
  • 514
  • 2
  • 6
  • 19
  • 1
    Check output of select gen_Test('english'); is it returning same respective column account_number, currency, trade_date ?. I think its function you have defined. You can write insert in function itself. – compyutech Dec 09 '15 at 06:29
  • @compyutech select gen_Test('english') is/will returning 'english' , as this passing name to cursor. About recommendation **having insert in function ** can be in case were result from function is static or for one table only. This function was designed to output the result of query in dynamic for any table with any columns and further utilize it for insertion to destination. As per your recommendation for case having 100 table will need to create hundred function for each table. – Harsimranjeet Singh Dec 09 '15 at 07:01
  • Your view on my recommendation is fine. But I have confusion regarding returning result from your function, If its not returning data in same column order, We can not insert in destination table. Can you share create statement of your function ? So that I can check it once from my side. – compyutech Dec 09 '15 at 07:28
  • @compyutech Create or replace Function gen_Test(query_name refcursor) returns refcursor as $$ Declare sql text; begin sql:=(SELECT 'SELECT '|| string_Agg(col_src,',') ||' FROM ' || tbl_src FROM md_formula WHERE format='Dbs' GROUP BY tbl_src); open query_name for execute sql; return query_name; end; $$ language plpgsql; select gen_Test('english'); fetch all in english; – Harsimranjeet Singh Dec 09 '15 at 07:39
  • 1
    Try changing `SELECT gen_Test(...)` to `SELECT * FROM gen_Test(...)`. The first form will never produce more than one column. – Nick Barnes Dec 09 '15 at 07:57
  • you are giving more col names in insert statement than the values.. try Selecting actual col names instead of using gen_test() – captain_a Dec 09 '15 at 08:21
  • @NickBarnes this second form also return one column only. – Harsimranjeet Singh Dec 09 '15 at 08:59
  • @captain_a just edit the post with "Just for Reference Output of above Query:" image which shows column in Insert and from select are same. – Harsimranjeet Singh Dec 09 '15 at 09:00

1 Answers1

0

You could try like so:

INSERT INTO normalized_transaction(account_number, currency, trade_date)
SELECT foo.*
FROM gen_Test('english') as foo;
MWiesner
  • 8,868
  • 11
  • 36
  • 70
Pr0100
  • 136
  • 4
  • SELECT foo.* FROM gen_Test('english') as foo; is returning "english" only. this is not a table valued function but a function calling cursor in it, with cursor name passing as a parameter. – Harsimranjeet Singh Dec 10 '15 at 05:56