Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc08857f

Browse files
author
Vladimir Ershov
committed
avoid use EXECUTE while simple select
1 parent36bf629 commitc08857f

File tree

2 files changed

+38
-18
lines changed

2 files changed

+38
-18
lines changed

‎pgpro_scheduler--2.0--2.1.sql‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -223,3 +223,29 @@ GRANT SELECT ON @extschema@.jobs_log TO public;
223223
ALTERTABLE @extschema@.at ADDPRIMARY KEY (start_at, cron);
224224
ALTERTABLE @extschema@.log ADDPRIMARY KEY (start_at, cron);
225225

226+
227+
DROPFUNCTION _get_array_from_jsonb(text[], jsonb);
228+
229+
CREATEFUNCTION_get_array_from_jsonb(dsttext[], src jsonb) RETURNStext[]AS
230+
$BODY$
231+
DECLARE
232+
vtypetext;
233+
BEGIN
234+
IF src ISNULL THEN
235+
RETURN dst;
236+
END IF;
237+
238+
SELECT INTO vtype jsonb_typeof(src);
239+
240+
IF vtype='string' THEN
241+
SELECT INTO dst array_append(dst, src->>0);
242+
ELSIF vtype='array' THEN
243+
SELECT INTO dst dst|| array_agg(value)::text[]from jsonb_array_elements_text(src);
244+
ELSE
245+
RAISE EXCEPTION'The value could be only''string'' or''array'' type';
246+
END IF;
247+
248+
RETURN dst;
249+
END
250+
$BODY$
251+
LANGUAGE plpgsqlset search_path TO @extschema@;

‎pgpro_scheduler--2.1.sql‎

Lines changed: 12 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -233,7 +233,7 @@ CREATE VIEW job_status AS
233233
resubmit_limit, postponeas max_wait_interval,
234234
max_run_timeas max_duration, submit_time, canceled,
235235
start_time, statusas is_success, reasonas error, done_time,
236-
'done'::job_at_status_t status
236+
'done'::@extschema@.job_at_status_t status
237237
FROM @extschema@.at_jobs_donewhere owner=session_user
238238
UNION
239239
SELECT
@@ -242,7 +242,7 @@ CREATE VIEW job_status AS
242242
resubmit_limit, postponeas max_wait_interval,
243243
max_run_timeas max_duration, submit_time, canceled, start_time,
244244
NULLas is_success,NULLas error,NULLas done_time,
245-
'processing'::job_at_status_t status
245+
'processing'::@extschema@.job_at_status_t status
246246
FROM ONLY @extschema@.at_jobs_processwhere owner=session_user
247247
UNION
248248
SELECT
@@ -252,7 +252,7 @@ CREATE VIEW job_status AS
252252
max_run_timeas max_duration, submit_time, canceled,
253253
NULLas start_time,NULLas is_success,NULLas error,
254254
NULLas done_time,
255-
'submitted'::job_at_status_t status
255+
'submitted'::@extschema@.job_at_status_t status
256256
FROM ONLY @extschema@.at_jobs_submittedwhere owner=session_user;
257257

258258
--
@@ -266,7 +266,7 @@ CREATE VIEW all_job_status AS
266266
attempt, resubmit_limit, postponeas max_wait_interval,
267267
max_run_timeas max_duration, submit_time, canceled,
268268
start_time, statusas is_success, reasonas error, done_time,
269-
'done'::job_at_status_t status
269+
'done'::@extschema@.job_at_status_t status
270270
FROM @extschema@.at_jobs_done
271271
UNION
272272
SELECT
@@ -275,7 +275,7 @@ CREATE VIEW all_job_status AS
275275
attempt, resubmit_limit, postponeas max_wait_interval,
276276
max_run_timeas max_duration, submit_time, canceled, start_time,
277277
NULLas is_success,NULLas error,NULLas done_time,
278-
'processing'::job_at_status_t status
278+
'processing'::@extschema@.job_at_status_t status
279279
FROM ONLY @extschema@.at_jobs_process
280280
UNION
281281
SELECT
@@ -285,7 +285,7 @@ CREATE VIEW all_job_status AS
285285
max_run_timeas max_duration, submit_time, canceled,
286286
NULLas start_time,NULLas is_success,NULLas error,
287287
NULLas done_time,
288-
'submitted'::job_at_status_t status
288+
'submitted'::@extschema@.job_at_status_t status
289289
FROM ONLY @extschema@.at_jobs_submitted;
290290

291291
---------------
@@ -608,27 +608,21 @@ END
608608
$BODY$
609609
LANGUAGE plpgsqlset search_path TO @extschema@;
610610

611-
CREATEFUNCTION_get_array_from_jsonb(dsttext[],value jsonb) RETURNStext[]AS
611+
CREATEFUNCTION_get_array_from_jsonb(dsttext[],src jsonb) RETURNStext[]AS
612612
$BODY$
613613
DECLARE
614614
vtypetext;
615615
BEGIN
616-
IFvalue ISNULL THEN
616+
IFsrc ISNULL THEN
617617
RETURN dst;
618618
END IF;
619619

620-
EXECUTE'SELECT jsonb_typeof($1)'
621-
INTO vtype
622-
USING value;
620+
SELECT INTO vtype jsonb_typeof(src);
621+
623622
IF vtype='string' THEN
624-
-- EXECUTE 'SELECT array_append($1, jsonb_set(''{"a":""}''::jsonb, ''{a}'', $2)->>''a'')'
625-
EXECUTE'SELECT array_append($1, $2->>0)'
626-
INTO dst
627-
USING dst, value;
623+
SELECT INTO dst array_append(dst, src->>0);
628624
ELSIF vtype='array' THEN
629-
EXECUTE'SELECT $1 || array_agg(value)::text[] from jsonb_array_elements_text($2)'
630-
INTO dst
631-
USING dst, value;
625+
SELECT INTO dst dst|| array_agg(value)::text[]from jsonb_array_elements_text(src);
632626
ELSE
633627
RAISE EXCEPTION'The value could be only''string'' or''array'' type';
634628
END IF;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp