Guest User

Untitled

a guest
Jul 19th, 2018
85
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.58 KB | None | 0 0
  1. CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
  2. DECLARE
  3. get404json text;
  4.  
  5. BEGIN
  6. get404json := 'insert into public.events_404_normalized(event_name) select json_data->>''event_name'' from public.event_404 WHERE id IN(select id from public.event_404 WHERE created_at < (select now()) AND processed is NULL)';
  7. EXECUTE format(get404json);
  8. RETURN NEW;
  9. END
  10. $$;
  11.  
  12. CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
  13. DECLARE
  14. get404ids text;
  15. result404ids int ARRAY;
  16. get404json text;
  17. mark404processed text;
  18.  
  19. BEGIN
  20. get404ids := 'select id from public.event_404 WHERE created_at < (select now()) AND processed is NULL';
  21. EXECUTE format(get404ids) INTO result404ids;
  22. get404json := 'insert into public.events_404_normalized(event_name) select json_data->>''event_name'' from public.event_404 WHERE id = ANY (result404ids)';
  23. EXECUTE format(get404json);
  24. mark404processed := 'UPDATE public.event_404 SET processed = TRUE WHERE id IN(result404ids)';
  25. RETURN NEW;
  26. END
  27. $$;
  28.  
  29. ERROR: malformed array literal: "51"
  30. DETAIL: Array value must start with "{" or dimension information.
  31. CONTEXT: PL/pgSQL function transform404activities() line 10 at EXECUTE
  32.  
  33. select id from public.event_404 WHERE created_at < (select now()) AND processed is NULL;
  34. id
  35. ----
  36. 51
  37. 52
  38. 53
  39. 50
  40. (4 rows)
  41.  
  42. CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
  43. DECLARE
  44. get404ids text;
  45. result404ids int ARRAY;
  46. get404json text;
  47. mark404processed text;
  48.  
  49. BEGIN
  50. get404ids := 'select array_agg(id) from public.event_404 WHERE created_at < (select now()) AND processed is NULL';
  51. EXECUTE format(get404ids) INTO result404ids;
  52. get404json := 'insert into public.events_404_normalized(event_name) select json_data->>''event_name'' from public.event_404 WHERE id = ANY (result404ids)';
  53. EXECUTE format(get404json);
  54. mark404processed := 'UPDATE public.event_404 SET processed = TRUE WHERE id IN(result404ids)';
  55. RETURN NEW;
  56. END
  57. $$;
  58.  
  59. ERROR: column "result404ids" does not exist
  60. LINE 1: ...event_name' from public.event_404 WHERE id = ANY (result404i...
  61. ^
  62. QUERY: insert into public.events_404_normalized(event_name) select json_data->>'event_name' from public.event_404 WHERE id = ANY (result404ids)
  63. CONTEXT: PL/pgSQL function transform404activities() line 12 at EXECUTE
  64.  
  65. select array_agg(id) from public.event_404 WHERE created_at < (select now()) AND processed is NULL;
  66. array_agg
  67. ---------------
  68. {51,52,53,50}
Add Comment
Please, Sign In to add comment