Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
- DECLARE
- get404json text;
- BEGIN
- 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)';
- EXECUTE format(get404json);
- RETURN NEW;
- END
- $$;
- CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
- DECLARE
- get404ids text;
- result404ids int ARRAY;
- get404json text;
- mark404processed text;
- BEGIN
- get404ids := 'select id from public.event_404 WHERE created_at < (select now()) AND processed is NULL';
- EXECUTE format(get404ids) INTO result404ids;
- get404json := 'insert into public.events_404_normalized(event_name) select json_data->>''event_name'' from public.event_404 WHERE id = ANY (result404ids)';
- EXECUTE format(get404json);
- mark404processed := 'UPDATE public.event_404 SET processed = TRUE WHERE id IN(result404ids)';
- RETURN NEW;
- END
- $$;
- ERROR: malformed array literal: "51"
- DETAIL: Array value must start with "{" or dimension information.
- CONTEXT: PL/pgSQL function transform404activities() line 10 at EXECUTE
- select id from public.event_404 WHERE created_at < (select now()) AND processed is NULL;
- id
- ----
- 51
- 52
- 53
- 50
- (4 rows)
- CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
- DECLARE
- get404ids text;
- result404ids int ARRAY;
- get404json text;
- mark404processed text;
- BEGIN
- get404ids := 'select array_agg(id) from public.event_404 WHERE created_at < (select now()) AND processed is NULL';
- EXECUTE format(get404ids) INTO result404ids;
- get404json := 'insert into public.events_404_normalized(event_name) select json_data->>''event_name'' from public.event_404 WHERE id = ANY (result404ids)';
- EXECUTE format(get404json);
- mark404processed := 'UPDATE public.event_404 SET processed = TRUE WHERE id IN(result404ids)';
- RETURN NEW;
- END
- $$;
- ERROR: column "result404ids" does not exist
- LINE 1: ...event_name' from public.event_404 WHERE id = ANY (result404i...
- ^
- QUERY: insert into public.events_404_normalized(event_name) select json_data->>'event_name' from public.event_404 WHERE id = ANY (result404ids)
- CONTEXT: PL/pgSQL function transform404activities() line 12 at EXECUTE
- select array_agg(id) from public.event_404 WHERE created_at < (select now()) AND processed is NULL;
- array_agg
- ---------------
- {51,52,53,50}
Add Comment
Please, Sign In to add comment