Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays
Thank you all for your help. I currently only have the two cases to handle, so I went with the below if-else statement which works how I expected. -- Generate array of tables to create if (create_source) then the_tables := array[[new_table_schema, new_table_name],[new_table_schema||'_source', new_table_name||'_source']]; else the_tables := array[[new_table_schema, new_table_name]]; end if; -- Michael Rasmussen Sr. Data Engineer Porch On 12/28/15, 3:51 PM, "Tom Lane" wrote: >"David G. Johnston" writes: >> ​or a more semantically meaning one...the use of the inner array is >> arguably a hack here meant to avoid the overhead and new type creation by >> assigning meaning to array slots. > >Yeah, good point: it looks like Mike does not consider the columns of >the array to be interchangeable at all, so really he would be better >off modeling the data as you suggest. > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays
"David G. Johnston" writes: > âor a more semantically meaning one...the use of the inner array is > arguably a hack here meant to avoid the overhead and new type creation by > assigning meaning to array slots. Yeah, good point: it looks like Mike does not consider the columns of the array to be interchangeable at all, so really he would be better off modeling the data as you suggest. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays
On Mon, Dec 28, 2015 at 4:39 PM, Tom Lane wrote: > Michael Rasmussen writes: > > I am trying to iterate through a multidimensional array using a foreach > loop, as exampled in the documentation at > http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY > . > > create type textarray as (t text[]); > or a more semantically meaning one...the use of the inner array is arguably a hack here meant to avoid the overhead and new type creation by assigning meaning to array slots. i.e., create type table_with_schema (name text, schema text); I would likewise attempt to do away with the outer array as well if the overall structure of the example follows reality closely enough. David J.
Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays
On Mon, Dec 28, 2015 at 4:05 PM, Michael Rasmussen
wrote:
> Hello, I’m running 9.4.5 locally on my mac doing some plpgsql development.
>
> I am trying to iterate through a multidimensional array using a foreach
> loop, as exampled in the documentation at
> http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY
> .
>
> Here is a simplified version of the function:
>
> CREATE OR REPLACE FUNCTION create_table(
> new_table_schema character varying,
> new_table_name character varying,
> create_log boolean DEFAULT true,
> create_source boolean DEFAULT false
> ) RETURNS void AS
> $BODY$
> declare
> the_tables text[][];
> the_table text[];
> begin
> -- Generate array of tables to create
> the_tables[1] := array[new_table_schema, new_table_name];
>
> if (create_source) then
> the_tables[2] := array[new_table_schema||'_source',
> new_table_name||'_source'];
> end if;
>
> RAISE NOTICE 'the_tables = %', the_tables;
>
> <>
> foreach the_table slice 1 in array the_tables
> loop
> raise notice 'schema = %; table = %', the_table[1], the_table[2];
> end loop BIGLOOP;
>
> end;
> $BODY$
> LANGUAGE plpgsql;
>
> When I run it, I get the following message output:
>
> NOTICE: the_tables = {"{mike,test}","{mike_source,test_source}"}
>
> NOTICE: schema = {mike,test}; table = {mike_source,test_source}
>
>
> I am expecting:
>
> NOTICE: the_tables = {{'mike','test'},{'mike_source','test_source'}}
>
> NOTICE: schema = mike; table = test
>
> NOTICE: schema = mike_source; table = test_source
>
> I suspect something is happening with the assignment operator :=, as those
> double quotes seem to indicate the subarrays are being cast to strings?
>
>
> I tried casting during the assignment, i.e. the_tables[1] :=
> array[new_table_schema, new_table_name]::text[], but that had no effect.
>
>
> Does anyone know what I might be doing wrong?
>
>
>
I cannot adequately explain the behavior though you are likely correct
that since the multi-dimensional array's type is text that the attempt to
assign an array to an element converts the array to text instead of
assigning the array.
Two suggestions:
1) Use the array modification operators defined here:
http://www.postgresql.org/docs/9.4/interactive/arrays.html#ARRAYS-MODIFYING
to perform the modifications and reassign the entire result back to the
variable.
2) Create a composite type which can then be a simple component of a
one-dimensional array.
I suggest doing both though either option might be workable alone if you
wish to try things out...
David J.
Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays
Michael Rasmussen writes:
> I am trying to iterate through a multidimensional array using a foreach loop,
> as exampled in the documentation at
> http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY.
> Here is a simplified version of the function:
> CREATE OR REPLACE FUNCTION create_table(
> new_table_schema character varying,
> new_table_name character varying,
> create_log boolean DEFAULT true,
> create_source boolean DEFAULT false
> ) RETURNS void AS
> $BODY$
> declare
> the_tables text[][];
> the_table text[];
> begin
> -- Generate array of tables to create
> the_tables[1] := array[new_table_schema, new_table_name];
> if (create_source) then
> the_tables[2] := array[new_table_schema||'_source',
> new_table_name||'_source'];
> end if;
That's not going to work, because it's a type violation.
Multi-dimensional arrays in PG are not arrays of arrays.
(Maybe they should have been, but it's too late to change that.)
The only reason you don't get an immediate runtime error is that
plpgsql is so lax about type coercions, and a text value will
accept pretty much anything.
The right way to do what you're trying to do is array slice assignment.
Ideally, you'd write the above like this:
-- Generate array of tables to create
the_tables[1:1][1:2] := array[new_table_schema, new_table_name];
if (create_source) then
the_tables[2:2][1:2] := array[new_table_schema||'_source',
new_table_name||'_source'];
end if;
Unfortunately, that's got two problems: no one's ever gotten around to
making array slice assignment syntax work at all in plpgsql, and even if
it did, the second assignment requires extension of an already-existing
array value, which we don't currently support for multi-D cases (that's
the core array code's fault not plpgsql's fault).
Both of those things could probably be made to happen if anyone cared
to put in the work, but that won't help you in existing releases.
A workaround I've seen used is to create a composite type, so that
what you have is 1-D arrays of composite types of 1-D arrays:
create type textarray as (t text[]);
CREATE OR REPLACE FUNCTION create_table(
new_table_schema character varying,
new_table_name character varying,
create_source boolean
) RETURNS void AS
$BODY$
declare
the_tables textarray[];
the_table textarray;
begin
-- Generate array of tables to create
the_tables[1] := row(array[new_table_schema, new_table_name])::textarray;
if (create_source) then
the_tables[2] := row(array[new_table_schema||'_source',
new_table_name||'_source'])::textarray;
end if;
RAISE NOTICE 'the_tables = %', the_tables;
<>
foreach the_table in array the_tables
loop
raise notice 'schema = %; table = %', the_table.t[1], the_table.t[2];
end loop BIGLOOP;
end;
$BODY$
LANGUAGE plpgsql;
select create_table('mike', 'test', true);
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays
Hello, I’m running 9.4.5 locally on my mac doing some plpgsql development.
I am trying to iterate through a multidimensional array using a foreach loop,
as exampled in the documentation at
http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY.
Here is a simplified version of the function:
CREATE OR REPLACE FUNCTION create_table(
new_table_schema character varying,
new_table_name character varying,
create_log boolean DEFAULT true,
create_source boolean DEFAULT false
) RETURNS void AS
$BODY$
declare
the_tables text[][];
the_table text[];
begin
-- Generate array of tables to create
the_tables[1] := array[new_table_schema, new_table_name];
if (create_source) then
the_tables[2] := array[new_table_schema||'_source',
new_table_name||'_source'];
end if;
RAISE NOTICE 'the_tables = %', the_tables;
<>
foreach the_table slice 1 in array the_tables
loop
raise notice 'schema = %; table = %', the_table[1], the_table[2];
end loop BIGLOOP;
end;
$BODY$
LANGUAGE plpgsql;
When I run it, I get the following message output:
NOTICE: the_tables = {"{mike,test}","{mike_source,test_source}"}
NOTICE: schema = {mike,test}; table = {mike_source,test_source}
I am expecting:
NOTICE: the_tables = {{'mike','test'},{'mike_source','test_source'}}
NOTICE: schema = mike; table = test
NOTICE: schema = mike_source; table = test_source
I suspect something is happening with the assignment operator :=, as those
double quotes seem to indicate the subarrays are being cast to strings?
I tried casting during the assignment, i.e. the_tables[1] :=
array[new_table_schema, new_table_name]::text[], but that had no effect.
Does anyone know what I might be doing wrong?
--
Michael Rasmussen
Sr. Data Engineer
Porch
Re: [GENERAL] Options for complex materialized views sharing most of the same logic?
On Sun, 2015-12-27 at 15:39 -0600, Wells Oliver wrote: > I then have four different views which do largely the same thing, > totaling the days four different ways: > > 1) by last 10 > 2) by the last 30 > 3) by the last 60 > 4) by the entire year > > Each of these views is basically a copy of one another for 99% of the > code (the summing, percentages, etc). The only differences are: > > 1) checks the days_back <= 10 > 2) checks days_back <= 30 > 3) checks days_back <= 60 > 4) does not check days_back > Hello, I do not follow why you have four views and presumably run four separate selects. Assuming one of the base tables to the view has a column containing a timestamp, why can't you define the column days_back in the view along the lines of:- select ((extract(epoch from current_date) - extract(epoch from tables_date_column)) / (24 * 60 * 60)) as days_back Then you can test days_back for the appropriate values using CASE, do the calculations, and end up running a single select query. Don't know if this helps. Cheers, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regex help again (sorry, I am bad at these)
Thank you Felix that was exactly what I needed!
-Chris
On Mon, Dec 28, 2015 at 2:23 PM, Félix GERZAGUET
wrote:
> Hello Chris,
>
> On Mon, Dec 28, 2015 at 8:10 PM, Christopher Molnar <
> cmol...@ourworldservices.com> wrote:
>
>> Any suggestions?
>>
> This seems to works:
>
> select regexp_replace('Complete the attached lab and submit via
> dropbox\rhttps://owncloud.porterchester.edu/HVACR/PCI_GasHeat/GasElectrical/HVACR1114_LAB_13A.pdf";
> title="Lab 13A">Lab 13A<\a>', '/([^/]*)\" title=', '/&file=\1" title=')
>
> Regards,
>
> Félix
>
>
Re: [GENERAL] Regex help again (sorry, I am bad at these)
Although, in this particular case, it is not version or O/S specific, it is generally a good policy (and manners) to state them whenever contacting this mail list. In that way, future users that refer back to problems have it documented as to which are and are not version specific. On Mon, Dec 28, 2015 at 2:42 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Dec 28, 2015 at 12:25 PM, Melvin Davidson > wrote: > >> Will this work? >> >> UPDATE your_table >>SET your_column = REPLACE (your_column, 'HVACR1114_LAB_13A.pdf', >> '&file=HVACR1114_LAB_13A.pdf') >> WHERE ; >> >> Your mileage may vary because you have not stated your VERSION of >> PostgreSQL or your O/S. >> >> > What part of your solution is version or O/S dependent? > > David J. > > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Regex help again (sorry, I am bad at these)
On Mon, Dec 28, 2015 at 12:25 PM, Melvin Davidson wrote: > Will this work? > > UPDATE your_table >SET your_column = REPLACE (your_column, 'HVACR1114_LAB_13A.pdf', > '&file=HVACR1114_LAB_13A.pdf') > WHERE ; > > Your mileage may vary because you have not stated your VERSION of > PostgreSQL or your O/S. > > What part of your solution is version or O/S dependent? David J.
Re: [GENERAL] Regex help again (sorry, I am bad at these)
On Mon, Dec 28, 2015 at 12:10 PM, Christopher Molnar <
cmol...@ourworldservices.com> wrote:
Given this...
> 'Complete the attached lab and submit via dropbox\rhttps://owncloud.porterchester.edu/HVACR/PCI_GasHeat/GasElectrical/HVACR1114_LAB_13A.pdf";
> title="Lab 13A">Lab 13A<\a>'
>
I have no clue how the following gives you any matches...
specifically the presence of the "$" after the title= causes the entire
pattern to always fail since that isn't the end of the string.
>
>
> update pcilms_assign set intro=regexp_replace(intro, '/([^/]*)\"
> title=$', '&files=\1') where intro like '%
> https://owncloud.porterchester.edu%' and course=18 and id=55413;
>
> and the result puts the &file= in the wrong place (at the end of the whole
> string).
>
The basic problem is that entirety of the content that your pattern
matches is replaced with the totality of the replacement expression.
Since you are matching the literal "title=" you have to somehow place that
same literal in the result. You can capture it and then use "\2" or you
can place it literally like Félix shows.
Alternatively, don't capture it. The way you match something without
capturing it is by using what is termed a "zero-width" expression or a
"look-around". In this case you want to "look-ahead" which is expressed
thusly: (?=)
So...
'/([^/]*)(?=" title=)'
SELECT regexp_replace('https://www.www.www/path/FILE.pdf";
title="FILE">', '/([^/]*)(?=" title=)', '&files=\1')
David J.
Re: [GENERAL] grep -f keyword data query
On Sun, Dec 27, 2015 at 3:53 PM, Hiroyuki Sato wrote: > Hello Andreas and Tom > > Thank you for replying. > > Sorry, I re-created my questions. I was mis-pasted query log on previous > question. > (@~ operator is PGroonga extension (http://pgroonga.github.io)) > Please ignore it. > > Best regards. > > 1, Problem. > (1) Following query is exteme slow. (478sec) > SELECT > u.url > FROM > url_lists4 u, > keywords4 k > WHERE > u.url like k.url > AND > k.name = 'esc_url'; > > > (2) grep -f kwd.txt sample.txt (exec time under 1sec) Certainly not in my hands. The best I can get is 9 seconds. > > 2, Questions > > (1) Is it possible to improve this query like the command ``grep -f > keyword data``? You will not get a general tool to match a specialized tool in the specialized tool's own domain. fgrep is good at what fgrep does. Since your queries all have constant text strings at the beginning, they could use the index. But if you are not using the C collation, then you need build a special index: create index on url_lists4 (url text_pattern_ops); But, the planner refuses to use this index for your query anyway, because it can't see that the patterns are all left-anchored. Really, your best bet is refactor your url data so it is stored with a url_prefix and url_suffix column. Then you can do exact matching rather than pattern matching. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regex help again (sorry, I am bad at these)
Will this work? UPDATE your_table SET your_column = REPLACE (your_column, 'HVACR1114_LAB_13A.pdf', '&file=HVACR1114_LAB_13A.pdf') WHERE ; Your mileage may vary because you have not stated your VERSION of PostgreSQL or your O/S. On Mon, Dec 28, 2015 at 2:10 PM, Christopher Molnar < cmol...@ourworldservices.com> wrote: > Sorry to have to ask the experts here for some regex assistance again. I > am admittadly awful with these and could use some help. > > Have the following string (this is an example) that needs to be changed. > Need to use a search and replace because the links across over 200K records > are similar but not the same. > > 'Complete the attached lab and submit via dropbox\rhttps://owncloud.porterchester.edu/HVACR/PCI_GasHeat/GasElectrical/HVACR1114_LAB_13A.pdf"; > title="Lab 13A">Lab 13A<\a>' > > > Need the final string to separate the "LAB_13A.pdf" from the rest of the > URL by inserting a "&file=" in front of it. The final string should look > like: > > > 'Complete the attached lab and submit via dropbox\rhttps://owncloud.porterchester.edu/HVACR/PCI_GasHeat/GasElectrical/&file=HVACR1114_LAB_13A.pdf"; > title="Lab 13A">Lab 13A<\a>' > > > I have tried something like: > > update pcilms_assign set intro=regexp_replace(intro, '/([^/]*)\" > title=$', '&files=\1') where intro like '% > https://owncloud.porterchester.edu%' and course=18 and id=55413; > > and the result puts the &file= in the wrong place (at the end of the whole > string). > > > Any suggestions? > > > Thanks! > > -Chris > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Regex help again (sorry, I am bad at these)
Hello Chris,
On Mon, Dec 28, 2015 at 8:10 PM, Christopher Molnar <
cmol...@ourworldservices.com> wrote:
> Any suggestions?
>
This seems to works:
select regexp_replace('Complete the attached lab and submit via
dropbox\rhttps://owncloud.porterchester.edu/HVACR/PCI_GasHeat/GasElectrical/HVACR1114_LAB_13A.pdf";
title="Lab 13A">Lab 13A<\a>', '/([^/]*)\" title=', '/&file=\1" title=')
Regards,
Félix
[GENERAL] Regex help again (sorry, I am bad at these)
Sorry to have to ask the experts here for some regex assistance again. I am admittadly awful with these and could use some help. Have the following string (this is an example) that needs to be changed. Need to use a search and replace because the links across over 200K records are similar but not the same. 'Complete the attached lab and submit via dropbox\rhttps://owncloud.porterchester.edu/HVACR/PCI_GasHeat/GasElectrical/HVACR1114_LAB_13A.pdf"; title="Lab 13A">Lab 13A<\a>' Need the final string to separate the "LAB_13A.pdf" from the rest of the URL by inserting a "&file=" in front of it. The final string should look like: 'Complete the attached lab and submit via dropbox\rhttps://owncloud.porterchester.edu/HVACR/PCI_GasHeat/GasElectrical/&file=HVACR1114_LAB_13A.pdf"; title="Lab 13A">Lab 13A<\a>' I have tried something like: update pcilms_assign set intro=regexp_replace(intro, '/([^/]*)\" title=$', '&files=\1') where intro like '%https://owncloud.porterchester.edu%' and course=18 and id=55413; and the result puts the &file= in the wrong place (at the end of the whole string). Any suggestions? Thanks! -Chris
Re: [GENERAL] grep -f keyword data query
Hiroyuki Sato writes: > I re-created index with pg_trgm. > Execution time is 210sec. > Yes It is faster than btree index. But still slow. > It is possible to improve this query speed? > Should I use another query or idex? Did you try a GIN index? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Options for complex materialized views sharing most of the same logic?
On Sun, Dec 27, 2015 at 10:39 PM, Wells Oliver wrote: > I have one view which totals about 60 columns per day. Each day has a "days > ago" column like so: . > Each of these views is basically a copy of one another for 99% of the code > (the summing, percentages, etc). The only differences are: > > 1) checks the days_back <= 10 > 2) checks days_back <= 30 > 3) checks days_back <= 60 > 4) does not check days_back > > Is there some easier way for me to maintain the structure of the view > without copying/pasting it 4 times and making one small tweak? I find myself > adding/removing columns to these views and I do it 4 times each time. Without knowing more details I would sugest making number 4 and then defining 1-3 as select * from v4 wher days_back<=. And, for the copy/paste stuff, you can do it easily with an script. I normally would do it in some scripting language but you can even do it in psql with some creative variable usage, just put the head stuf in a var, the tail in another one and compose the sentences using them, simplified example: s=> \set head 'select version() where 2>1' s=> \set tail 'and 3>1' s=> :head :tail; version PostgreSQL 9.3.10 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (Gentoo 4.9.3 p1.4, pie-0.6.4) 4.9.3, 64-bit (1 row) s=> :head and 0>1 :tail ; version - (0 rows) If posible I would try the composite stuff mentioned first, but one of these should be enough, in the second case you still recreate the things, but let the macro processor do the boring stuff. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Calling function (table_name, schema_name) within event trigger
Susan Hurst wrote: > h...well, Tom, at least you saved me a lot of frustration with > trying to get this to work :-) For the time being, I'll just follow up > DDL activity with a procedure that compares diffs between > information_schema and the history tables. If and when pl/pgsql offers > the capture of DDL activity on the fly, I can just move my code to an > event trigger. This works in 9.5: CREATE FUNCTION test_event_trigger_for_create() RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE obj record; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE NOTICE '% created object: %', tg_tag, obj.object_identity; END LOOP; END $$; CREATE EVENT TRIGGER test_event_trigger_for_create ON ddl_command_end EXECUTE PROCEDURE test_event_trigger_for_create(); Demo: test=*# create table foo (i int); NOTICE: CREATE TABLE created object: public.foo CREATE TABLE test=*# Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
