Guest User

Untitled

a guest
Feb 21st, 2018
254
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.63 KB | None | 0 0
  1. [
  2. { col1: a, col2: 5, col3: 1, col4: one},
  3. { col1: b, col2: 6, col3: 2, col4: two},
  4. { col1: c, col2: 7, col3: 3, col4: three},
  5. { col1: d, col2: 8, col3: 4, col4: four},
  6. ]
  7.  
  8. col1 (varchar)| col2 (integer) | col3 (integer) | col4 (varchar)
  9. -----------------+----------------+--------------------+------------------
  10. a | 5 | 1 | one
  11. b | 6 | 2 | two
  12. c | 7 | 3 | three
  13. d | 8 | 4 | four
  14.  
  15. CREATE OR REPLACE FUNCTION UPDATE_TABLE_FUNC (
  16. arrayOfValues TEXT[]
  17. )
  18. RETURN VOID AS $$
  19. BEGIN
  20. UPDATE table SET (col1, col2, col3, col4) = ($1)
  21. END;
  22. $$ LANGUAGE plpgsql;
  23.  
  24. SELECT * FROM jsonb_to_recordset('[
  25. { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
  26. { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
  27. { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
  28. { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
  29. ]'::jsonb) AS t (col1 text, col2 integer, col3 integer, col4 text);
  30.  
  31. col1 │ col2 │ col3 │ col4
  32. ──────┼──────┼──────┼───────
  33. a │ 1 │ 1 │ one
  34. b │ 2 │ 2 │ two
  35. c │ 3 │ 3 │ three
  36. d │ 4 │ 4 │ four
  37.  
  38. CREATE TABLE inputtable (col1 text, col2 integer, col3 integer, col4 text);
  39.  
  40. SELECT * FROM jsonb_populate_recordset(NULL::yourtable, '[
  41. { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
  42. { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
  43. { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
  44. { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
  45. ]'::jsonb);
  46.  
  47. col1 │ col2 │ col3 │ col4
  48. ──────┼──────┼──────┼───────
  49. a │ 1 │ 1 │ one
  50. b │ 2 │ 2 │ two
  51. c │ 3 │ 3 │ three
  52. d │ 4 │ 4 │ four
  53.  
  54. WITH source AS (SELECT * FROM jsonb_populate_recordset [...])
  55. UPDATE yourtable
  56. SET col1 = s.col1, col2 = s.col2
  57. FROM source AS s
  58. WHERE col3 = s.col3;
  59.  
  60. databases=# set var `cat /home/user/file.json`
  61.  
  62. databases=# echo :'var';
  63.  
  64. databases=# create table code (name text not null, lastname text not null);
  65.  
  66. the names of the columns have to be the same as your json file, for example:
  67. [
  68. {
  69. "name": "enrique",
  70. "lastname": "vega"
  71. },
  72. {
  73. "name": "jose",
  74. "lastname": "mendez"
  75. }
  76. ]
  77.  
  78. databases=# insert into nametable select * from jsonb_populate_recordset(null::nametable,:'var'::jsonb);
Add Comment
Please, Sign In to add comment