sql - PostgreSQL : Optimizing function with virtuals tables without "UNION SELECT" -

i'm wondering if possible optimize function without creating table , indexes.

this function :

create or replace function decode_trame_v3(in tid integer)   returns table(id integer, card numeric, kilo double precision) $body$ declare     v_weights character varying;     v_waste_no integer = 0;     v_number_waste integer; begin  select weights data data.id = tid v_weights; select coalesce(length(v_weights)/7, 0) v_number_waste; loop exit when v_number_waste = v_waste_no; return query convert_table (letter, value) ( select '-' letter, 63 value union select ':', 62 union select 'z', 61 union select 'y', 60 union select 'x', 59 union select 'w', 58 union select 'v', 57 union select 'u', 56 union select 't', 55 union select 's', 54 union select 'r', 53 union select 'q', 52 union select 'p', 51 union select 'o', 50 union select 'n', 49 union select 'm', 48 union select 'l', 47 union select 'k', 46 union select 'j', 45 union select 'i', 44 union select 'h', 43 union select 'g', 42 union select 'f', 41 union select 'e', 40 union select 'd', 39 union select 'c', 38 union select 'b', 37 union select 'a', 36 union select 'z', 35 union select 'y', 34 union select 'x', 33 union select 'w', 32 union select 'v', 31 union select 'u', 30 union select 't', 29 union select 's', 28 union select 'r', 27 union select 'q', 26 union select 'p', 25 union select 'o', 24 union select 'n', 23 union select 'm', 22 union select 'l', 21 union select 'k', 20 union select 'j', 19 union select 'i', 18 union select 'h', 17 union select 'g', 16 union select 'f', 15 union select 'e', 14 union select 'd', 13 union select 'c', 12 union select 'b', 11 union select 'a', 10 union select '9', 9 union select '8', 8 union select '7', 7 union select '6', 6 union select '5', 5 union select '4', 4 union select '3', 3 union select '2', 2 union select '1', 1 union select '0', 0 ) select tid id, (((select value convert_table letter =substring(v_weights 4+(v_waste_no*7) 1)::character varying) % 8)*2 +  trunc((select value convert_table letter =substring(v_weights 5+(v_waste_no*7) 1)::character varying)/32, 0) ) * 1 +  (((select value convert_table letter =substring(v_weights 3+(v_waste_no*7) 1)::character varying) % 2)*8 + trunc((select value convert_table letter =substring(v_weights 4+(v_waste_no*7) 1)::character varying) / 8, 0) )*16 + (trunc((select value convert_table letter =substring(v_weights 3+(v_waste_no*7) 1)::character varying) / 2, 0) - trunc((select value convert_table letter =substring(v_weights 3+(v_waste_no*7) 1)::character varying) /32 ,0)*16 ) * 256 + (((select value convert_table letter =substring(v_weights 2+(v_waste_no*7) 1)::character varying) % 8 ) * 2 + trunc((select value convert_table letter =substring(v_weights 3+(v_waste_no*7) 1)::character varying) / 32, 0) ) * 4096 + (trunc((select value convert_table letter =substring(v_weights 2+(v_waste_no*7) 1)::character varying) / 8, 0) + ((select value convert_table letter = substring(v_weights 1+(v_waste_no*7) 1)::character varying) % 2) * 8 ) * 65536 + (trunc((select value convert_table letter = substring(v_weights 1+(v_waste_no*7) 1)::character varying) / 2, 0) ) * 1048576 card, (((select value convert_table letter =substring(v_weights 6+(v_waste_no*7) 1)::character varying) % 16) * 64 + (select value convert_table letter =substring(v_weights 7+(v_waste_no*7) 1)::character varying)) * 50 /1000::real kilo;   v_waste_no = v_waste_no + 1;  end loop; end; $body$   language plpgsql volatile   cost 100   rows 1000; 

when invoke function query, takes around 10 seconds :

select (decode_trame_v3(id)).* data limit 1000 

and have simplified code new function :

create or replace function decode_trame_v3(in tid integer)   returns table(id integer, card numeric, kilo double precision) $body$ declare     v_weights character varying;     v_waste_no integer = 0;     v_number_waste integer; begin  select weights data data.id = tid v_weights; select coalesce(length(v_weights)/7, 0) v_number_waste; loop exit when v_number_waste = v_waste_no; return query convert_table (letter, value) ( select '-' letter, 63 value union select ':', 62 union select 'z', 61 union select 'y', 60 union select 'x', 59 union select 'w', 58 union select 'v', 57 union select 'u', 56 union select 't', 55 union select 's', 54 union select 'r', 53 union select 'q', 52 union select 'p', 51 union select 'o', 50 union select 'n', 49 union select 'm', 48 union select 'l', 47 union select 'k', 46 union select 'j', 45 union select 'i', 44 union select 'h', 43 union select 'g', 42 union select 'f', 41 union select 'e', 40 union select 'd', 39 union select 'c', 38 union select 'b', 37 union select 'a', 36 union select 'z', 35 union select 'y', 34 union select 'x', 33 union select 'w', 32 union select 'v', 31 union select 'u', 30 union select 't', 29 union select 's', 28 union select 'r', 27 union select 'q', 26 union select 'p', 25 union select 'o', 24 union select 'n', 23 union select 'm', 22 union select 'l', 21 union select 'k', 20 union select 'j', 19 union select 'i', 18 union select 'h', 17 union select 'g', 16 union select 'f', 15 union select 'e', 14 union select 'd', 13 union select 'c', 12 union select 'b', 11 union select 'a', 10 union select '9', 9 union select '8', 8 union select '7', 7 union select '6', 6 union select '5', 5 union select '4', 4 union select '3', 3 union select '2', 2 union select '1', 1 union select '0', 0 ), separate_weights(coordonate, letter) ( select 'k21', (select value convert_table letter = substring(v_weights 1 +(v_waste_no*7) 1)::character varying) union select 'l21', (select value convert_table letter =substring(v_weights 2 +(v_waste_no*7) 1)::character varying) union select 'm21', (select value convert_table letter =substring(v_weights 3 +(v_waste_no*7) 1)::character varying) union select 'n21', (select value convert_table letter =substring(v_weights 4 +(v_waste_no*7) 1)::character varying) union select 'o21', (select value convert_table letter =substring(v_weights 5 +(v_waste_no*7) 1)::character varying) union select 'p21', (select value convert_table letter =substring(v_weights 6 +(v_waste_no*7) 1)::character varying) union select 'q21', (select value convert_table letter =substring(v_weights 7 +(v_waste_no*7) 1)::character varying) ), calc_weights(coordonate, value) ( select 's21', trunc(( select letter separate_weights coordonate = 'k21') / 2, 0) union select 't21', trunc(( select letter separate_weights coordonate = 'l21') / 8, 0) + (( select letter separate_weights coordonate = 'k21') % 2) * 8 union select 'u21', (( select letter separate_weights coordonate = 'l21') % 8) * 2 + trunc(( select letter separate_weights coordonate = 'm21') / 32, 0) union select 'v21', trunc(( select letter separate_weights coordonate = 'm21') / 2, 0) - trunc(( select letter separate_weights coordonate = 'm21') / 32, 0) * 16 union select 'w21', (( select letter separate_weights coordonate = 'm21') % 2) * 8 + trunc(( select letter separate_weights coordonate = 'n21') / 8, 0) union select 'x21', (( select letter separate_weights coordonate = 'n21') % 8) * 2 + trunc(( select letter separate_weights coordonate = 'o21') / 32, 0) ), card(card) ( select (     (select value calc_weights coordonate = 'x21') * 1 +     (select value calc_weights coordonate = 'w21') * 16 +     (select value calc_weights coordonate = 'v21') * 256 +     (select value calc_weights coordonate = 'u21') * 4096 +     (select value calc_weights coordonate = 't21') * 65536 +     (select value calc_weights coordonate = 's21') * 1048576     ) ), kilo(kilo) ( select ( ((( select letter separate_weights coordonate = 'p21') % 16) * 64 + ( select letter separate_weights coordonate = 'q21')) * 50 /1000::double precision ) )  select tid id, (select card.card card) card, (select kilo.kilo kilo) kilo;  v_waste_no = v_waste_no + 1;  end loop; end; $body$   language plpgsql volatile   cost 100   rows 1000; 

i thinking faster new function takes around 12-13 seconds (always same query).

it seems "union select" slowing query.

what speed query without creating tables or indexes ?

many in advance !

edit :

i find question imprecise , large, have cut question simple question :

i'm wondering if possible create virtual tables without "union select" ?

i don't want create tables or temp tables.

now creating virtual tables data, use :

with convert_table (letter, value) ( select '-' letter, 63 value union select ':', 62 union select 'z', 61 union select 'y', 60 union select 'x', 59 union select 'w', 58 union select 'v', 57 union select 'u', 56 union select 't', 55 union select 's', 54 union select 'r', 53 union select 'q', 52 union select 'p', 51 ...) select * convert_table 

i dont' find beautiful , easy use.

is there other methods to same job ?

sorry new people cames after !

a rather radical cleanup makes this:

create or replace function decode_trame_v3(tid integer)   returns table(id integer, card numeric, kilo double precision) $body$ declare     v_weights character varying;     v_waste_no integer := 0;     v_number_waste integer;     cvt char[];     v1 int;     v2 int;     v3 int;     v4 int;     v5 int;     v6 int;     v7 int; begin   select weights v_weights data data.id = tid;   v_number_waste := coalesce(length(v_weights)/7, 0) * 7;   cvt := '[0:63]={0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,'                  'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,:,-}';    id := tid;   loop     exit when v_waste_no = v_number_waste;     v1 := array_position(cvt, substring(v_weights 1+v_waste 1));     v2 := array_position(cvt, substring(v_weights 2+v_waste 1));     v3 := array_position(cvt, substring(v_weights 3+v_waste 1));     v4 := array_position(cvt, substring(v_weights 4+v_waste 1));     v5 := array_position(cvt, substring(v_weights 5+v_waste 1));     v6 := array_position(cvt, substring(v_weights 6+v_waste 1));     v7 := array_position(cvt, substring(v_weights 7+v_waste 1));      card := (v4 % 8) * 2 +              trunc(v5/32, 0) * 1 +              (v5 % 2) * 8 +             trunc(v4 / 8, 0) * 16 +             trunc(v3 / 2, 0) -             trunc(v3 /32 ,0) * 16 * 256 +             (v2 % 8 ) * 2 +             trunc(v3 / 32, 0) * 4096 +             trunc(v2 / 8, 0) +             (v1 % 2) * 8 * 65536 +             trunc(v1 / 2, 0) * 1048576;      kilo := (v6 % 16) * 64 + v7 * 50 / 1000.;      return next;     v_waste_no = v_waste_no + 7;   end loop;   return; end; $body$ language plpgsql volatile cost 100 rows 1000; 

first of all, entire cte has become single array , constructed outside of loop, gets created once. inside loop find "value", array index array_position(). because have multiple calls same arguments have made 7 variables v1..v7 hold array indexes. selects have been replaced simple assignment , loop becomes simple , readable. variable v_waste_number gets incremented 7 can forget multiplications.

note array_position() new in pg 9.5. if have older version, should make ctv char(64) := '0123...' , use:

v1 := position(substring(v_weights 1+v_waste 1) in cvt) - 1; ... 

this going slower, hard how much. note - 1 @ end make result 0-based.

now can "see" algorithm again , better check because may have removed few many parentheses (although don't think did).