normalizing a float array?
Hi - I can write a stored procedure to normalize float arrays, but is there a nicer way of doing this in postgresql?
Uppercase version of ß desired
HI:
I would be really happy if postgresql had an upper case version of the ß
german character. The wiki page
https://en.wikipedia.org/wiki/%C3%9F
indicates that the capital (U+1E9E ẞ LATIN CAPITAL LETTER SHARP S) was
encoded <https://en.wikipedia.org/wiki/Codepoint> by ISO 10646
<https://en.wikipedia.org/wiki/ISO_10646> in 2008.
BTW the reason that I'd like upper('ß') to give something different than
'ß' is because I have written a simple substitution puzzle for a large
number of languages where I show the encrypted lower case words in upper
case and the successful letter substitution submissions in lower case - so
I need the upper and lower case versions of each letter to be different!
Thanks for any assistance! Maybe I can hack what I want in python (which is
what I am using for the puzzle).
Celia McInnis
Re: Uppercase version of ß desired
Hmmm. Yes the unicode rules seem to be a little strict on conforming to
the past! I just made the following fix to my python code in forming the
upper case alphabet from the lower case one:
uc_alphabet = lc_alphabet.replace('ß', 'ẞ').upper()
So far I have only found German to have a lower case letter which has the
same value for its upper cased one.
Thanks,
Celia McInnis
.
On Mon, Mar 13, 2023 at 6:54 PM Tom Lane wrote:
> "Peter J. Holzer" writes:
> > On 2023-03-13 17:38:51 -0400, Celia McInnis wrote:
> >> I would be really happy if postgresql had an upper case version of the ß
> >> german character.
>
> > But the 'ß' is a bit special as it is usually uppercased to 'SS'
> > (although 'ẞ' is now officially allowed, too).
> > Apparently your (and my) locale doesn't uppercase ß at all, which isn't
> > correct according to German spelling rules but was very common in the
> > last decades.
>
> Our code for libc locales doesn't support upcasing 'ß' to 'SS',
> because it uses towlower() which can only manage
> one-character-to-one-character transformations. It should work for
> upcasing to 'ẞ', but as you say, you need to find a locale that thinks
> that should happen.
>
> You might have better luck if you have a version of Postgres that
> supports ICU and you can use an ICU locale. That code path doesn't
> appear to have any hard-wired assumption about how many characters
> in convert to how many out.
>
> regards, tom lane
>
Re: Uppercase version of ß desired
Thanks Philip. Certainly interesting, As long as postgresql and python
return something different for upper and lower case versions of these
letters and python indexing of the string picks out the characters
"properly" then it might not require a python fix for me.
What a can of worms! But at least postgresql and python do far better with
unicode than mysql and perl did! :-)
Celia McInnis
On Tue, Mar 14, 2023 at 9:12 AM Philip Semanchuk <
phi...@americanefficient.com> wrote:
>
>
> > On Mar 13, 2023, at 5:38 PM, Celia McInnis
> wrote:
> >
> > HI:
> >
> > I would be really happy if postgresql had an upper case version of the
> ß german character. The wiki page
> > https://en.wikipedia.org/wiki/%C3%9F
> >
> > indicates that the capital (U+1E9E ẞ LATIN CAPITAL LETTER SHARP S) was
> encoded by ISO 10646 in 2008.
> >
> > BTW the reason that I'd like upper('ß') to give something different than
> 'ß' is because I have written a simple substitution puzzle for a large
> number of languages where I show the encrypted lower case words in upper
> case and the successful letter substitution submissions in lower case - so
> I need the upper and lower case versions of each letter to be different!
> >
> > Thanks for any assistance! Maybe I can hack what I want in python (which
> is what I am using for the puzzle).
>
> Hi Celia,
> I ran into this too back when we were transitioning from Python 2 to 3 (2
> behaved differently from 3). While researching it I discovered this Python
> issue which maybe sheds some additional light on the subject:
> https://github.com/python/cpython/issues/74993
>
> We ultimately found 90 characters that (under Python 3) grew longer when
> uppercased.
>
> python -c "print([c for c in range(0x80, 0x22ff) if len(chr(c)) !=
> len(chr(c).upper())])”
>
>
> I hope this is at least interesting. :-)
>
> Cheers
> Philip
>
>
>
How to store in hours:minutes:seconds where hours may be bigger than 24
Hi: I want to store times in a database as hours:minutes:seconds where hours can be greater than 24. How do I do this? I will want to be able to add such times. Thanks, Celia McInnis
Re: How to store in hours:minutes:seconds where hours may be bigger than 24
Thanks for the suggestion, Steve, but No - when I insert 25:17:07::interval
into my table I get 01:17:07 into the table - i.e., it replaces 25 hours by
(25 mod 24) hours or 1 hour, and this is not what I want. I really need the
number of hours rather than the number of hours mod 24. Do I have to make a
composite type to get what I want???
Thanks,
Celia McInnis
On Tue, Mar 19, 2024 at 10:44 PM Steve Baldwin
wrote:
> Could you use an interval data type? For example:
>
> b2bcreditonline=# create table interval_example (i interval);
> CREATE TABLE
> b2bcreditonline=# insert into interval_example values
> ('26:15:32'::interval);
> INSERT 0 1
> b2bcreditonline=# select * from interval_example;
> i
> --
> 26:15:32
> (1 row)
>
> b2bcreditonline=# select i, i + interval '45 minutes' as plus from
> interval_example;
> i | plus
> --+--
> 26:15:32 | 27:00:32
> (1 row)
>
> Steve
>
> On Wed, Mar 20, 2024 at 1:05 PM Celia McInnis
> wrote:
>
>> Hi:
>>
>> I want to store times in a database as hours:minutes:seconds where hours
>> can be greater than 24. How do I do this? I will want to be able to add
>> such times.
>>
>> Thanks,
>> Celia McInnis
>>
>
Re: How to store in hours:minutes:seconds where hours may be bigger than 24
Whoops - I hadn't changed the type of the column in the table that I was
inserting into - it was of type "TIME WITHOUT TIMEZONE". Now that I have
set the column type to INTERVAL, I can insert the string '25:17:07' into
the column without even needing to do any casting.
Thank goodness and thanks!
Celia
On Tue, Mar 19, 2024 at 11:01 PM Christophe Pettus wrote:
>
>
> > On Mar 19, 2024, at 19:56, Celia McInnis
> wrote:
> >
> > Thanks for the suggestion, Steve, but No - when I insert
> 25:17:07::interval into my table I get 01:17:07 into the table - i.e., it
> replaces 25 hours by (25 mod 24) hours or 1 hour, and this is not what I
> want. I really need the number of hours rather than the number of hours mod
> 24. Do I have to make a composite type to get what I want???
>
> I'm not seeing that result:
>
> xof=# create table t (i interval);
> CREATE TABLE
> xof=# insert into t values('25:17:07'::interval);
> INSERT 0 1
> xof=# select * from t;
> i
> --
> 25:17:07
> (1 row)
>
> Can you show what you are doing that gets the result you describe?
Dropping a temporary view?
Hi I am using postresql 16, am trying to use temporary views in a piece of software that I am writing, and would like it to be able to drop and recreate temporary views. It seems from the documentation that I can only use "CREATE OR REPLACE TEMPORARY VIEW" if the replacement view has the same columns, so Is there a correct way to drop a temporary view? I can create a temporary view, but get a syntax error when I do what I thought would drop it. Here is a simple example of what doesn't work: tt=# create temporary view tempview as select now() as junk; CREATE VIEW tt=# select * from tempview; junk --- 2024-03-20 14:21:27.441168+00 (1 row) tt=# drop temporary view tempview; ERROR: syntax error at or near "temporary" LINE 1: drop temporary view tempview; ^ Also, when I then tried (I formerly had a non-temporary view called tempview) DROP VIEW tempview; DROP VIEW postgresql did that successfully, but when I then did select * from tempview: postgresql hung for a long time (more than 7 minutes) before returning the contents of some previous view tempview (a previous (temporary, I guess) view by that name that was created by my software when I was not creating a temporary view?). I really wasn't expecting this, so if someone can explain, that would be great. Thanks, Celia McInnis
Re: Dropping a temporary view?
Ok, thanks - so I guess that means that if there is both a temporary and a non temporary view called "tempvie", DROP VIEW tempview; will remove the 1st tempview found, which with my path is the temporary one. Is there some reason why it then took 7 minutes to select from the non-temporary view tempview after I dropped the temporary view tempview? I have sometimes had some very long query times when running query software, and maybe they are resulting from my switching between temporary and non-temporary views of the same name while debugging. If so, is there something I should be doing to clean up any temporary messes I am creating? Thanks, Celia McInnis On Wed, Mar 20, 2024 at 11:12 AM Erik Wienhold wrote: > On 2024-03-20 15:58 +0100, Celia McInnis wrote: > > I am using postresql 16, am trying to use temporary views in a piece of > > software that I am writing, and would like it to be able to drop and > > recreate temporary views. It seems from the documentation that I can only > > use "CREATE OR REPLACE TEMPORARY VIEW" if the replacement view has the > same > > columns, so Is there a correct way to drop a temporary view? > > > > I can create a temporary view, but get a syntax error when I do what I > > thought would drop it. Here is a simple example of what doesn't work: > > > > tt=# create temporary view tempview as select now() as junk; > > CREATE VIEW > > tt=# select * from tempview; > > junk > > --- > > 2024-03-20 14:21:27.441168+00 > > (1 row) > > > > tt=# drop temporary view tempview; > > ERROR: syntax error at or near "temporary" > > LINE 1: drop temporary view tempview; > > ^ > > It's just DROP VIEW for normal and temporary views. > > > Also, when I then tried (I formerly had a non-temporary view called > > tempview) > > > > DROP VIEW tempview; > > DROP VIEW > > > > postgresql did that successfully, but when I then did > > > > select * from tempview: > > > > postgresql hung for a long time (more than 7 minutes) before returning > the > > contents of some previous view tempview (a previous (temporary, I guess) > > view by that name that was created by my software when I was not > creating a > > temporary view?). I really wasn't expecting this, so if someone can > > explain, that would be great. > > The first view must have been a regular (non-temporary) one. It is then > possible to create a temporary view of the same name that shadows the > original view if pg_temp is searched first, which is the default if you > haven't modified search_path. But it's not possible to create a second > temporary view of the same name because they live in the same namespace > (pg_temp_N): > > regress=# create view tempview as select 1 a; > CREATE VIEW > regress=# select * from tempview; > a > --- > 1 > (1 row) > > regress=# create temp view tempview as select 2 b; > CREATE VIEW > regress=# select * from tempview; > b > --- > 2 > (1 row) > > regress=# create temp view tempview as select 3 c; > ERROR: relation "tempview" already exists > regress=# select * from tempview; > b > --- > 2 > (1 row) > > regress=# drop view tempview; > DROP VIEW > regress=# select * from tempview; > a > --- > 1 > (1 row) > > -- > Erik >
Re: Dropping a temporary view?
The view is being used in some web query software that multiple people will be accessing and the contents of the view depend on what the person is querying, so I think that temporary views or tables are a good idea. I change to non-temporary views or tables (in a test version of the software which is not web-crawl-able) when I'm trying to debug things, and I guess I have to be careful to clean those up when I switch back to the temporary tables/views. On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver wrote: > On 3/20/24 08:39, Celia McInnis wrote: > > Ok, thanks - so I guess that means that if there is both a temporary and > > a non temporary view called "tempvie", > > > > DROP VIEW tempview; > > > > will remove the 1st tempview found, which with my path is the temporary > > one. Is there some reason why it then took 7 minutes to select from the > > non-temporary view tempview after I dropped the temporary view tempview? > > > > I have sometimes had some very long query times when running query > > software, and maybe they are resulting from my switching between > > temporary and non-temporary views of the same name while debugging. If > > so, is there something I should be doing to clean up any temporary > > messes I am creating? > > What is the purpose of the temp view over the the regular view process? > > How do they differ in data? > > Is all the above happening in one session? > > Have you run EXPLAIN ANALYZE on the select from the regular view? > > > > > Thanks, > > Celia McInnis > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Dropping a temporary view?
Good, that's what I'd hope. I'm still not sure why it took more than 7 minutes in psql to select the old non-temporary view contents after dropping the newer temporary view of the same name. There were no delays in producing the original non-temporary view. If I can reproduce the problem in psql, I'll re-ask. Meanwhile I'll also change my software to use different view names when using non-temporary iviews for debugging. Thanks, Celia McInnis On Wed, Mar 20, 2024 at 12:33 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, March 20, 2024, Celia McInnis > wrote: >> >> >> Is there some reason why it then took 7 minutes to select from the >> non-temporary view tempview after I dropped the temporary view tempview? >> >>> >>> > The fact that you had and then dropped the temporary view has no > relationship to how some other unrelated view performs. That the views > have the same name is just bad naming/design for this very reason; it harms > understanding. > > David J. > >
Re: Dropping a temporary view?
Hi Adrian The only behaviour changed for the debugging was to make the view non-temporary, so that I could verify in psql that the content of the view was what I wanted it to be. Debugging CGI software can be quite difficult, so it's always good to have debugging hooks as a part of the software - I know that I always have a DEBUG flag which, if on, prints out all kinds of stuff into a debug file, and I just had my software set a different name for DEBUG mode's non-temporary view than I was using for the temporary view, as advised by Christophe Pettus. No, unfortunately I didn't do an explain on the slow query - and it's too late now since the views are removed. However, I never had a delay when waiting for the view to be created in my web software, so, I'll just proceed being more careful and hope that the delay seen was due to some big mess I created. Thanks, Celia McInnis On Wed, Mar 20, 2024 at 1:01 PM Adrian Klaver wrote: > On 3/20/24 09:51, Celia McInnis wrote: > > The view is being used in some web query software that multiple people > > will be accessing and the contents of the view depend on what the person > > is querying, so I think that temporary views or tables are a good idea. > > I change to non-temporary views or tables (in a test version of the > > software which is not web-crawl-able) when I'm trying to debug things, > > and I guess I have to be careful to clean those up when I switch back to > > the temporary tables/views. > > Why change behavior for the tests? Seems that sort of negates the value > of the testing. > > Have you run EXPLAIN ANALYZE on the problem query? > > > > > > > > > > On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 3/20/24 08:39, Celia McInnis wrote: > > > Ok, thanks - so I guess that means that if there is both a > > temporary and > > > a non temporary view called "tempvie", > > > > > > DROP VIEW tempview; > > > > > > will remove the 1st tempview found, which with my path is the > > temporary > > > one. Is there some reason why it then took 7 minutes to select > > from the > > > non-temporary view tempview after I dropped the temporary view > > tempview? > > > > > > I have sometimes had some very long query times when running query > > > software, and maybe they are resulting from my switching between > > > temporary and non-temporary views of the same name while > > debugging. If > > > so, is there something I should be doing to clean up any temporary > > > messes I am creating? > > > > What is the purpose of the temp view over the the regular view > process? > > > > How do they differ in data? > > > > Is all the above happening in one session? > > > > Have you run EXPLAIN ANALYZE on the select from the regular view? > > > > > > > > Thanks, > > > Celia McInnis > > > > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Dropping a temporary view?
On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver wrote: > > On 3/20/24 10:54 AM, Celia McInnis wrote: > > Comments below more to sort out the process in my head then anything else. > > Hi Adrian > > The only behaviour changed for the debugging was to make the view > non-temporary, so that I could verify in psql that the content of the view > was what I wanted it to be. Debugging CGI software can be quite difficult, > so it's always good to have debugging hooks as a part of the software - I > know that I always have a DEBUG flag which, if on, prints out all kinds of > stuff into a debug file, and I just had my software set a different name > for DEBUG mode's non-temporary view than I was using for the temporary > view, as advised by Christophe Pettus. > > This indicates you are working in different sessions and therefore > creating a regular view to see the same data in all sessions. > > Previously this regular view was named the same as the temporary view you > create in the production database. > > Now you name that regular view a unique name not to conflict with the > temporary view name(s). > > No, unfortunately I didn't do an explain on the slow query - and it's too > late now since the views are removed. However, I never had a delay when > waiting for the view to be created in my web software, so, I'll just > proceed being more careful and hope that the delay seen was due to some big > mess I created. > > In your original post you say the delay occurred on a SELECT not a CREATE > VIEW after: > Correct. But the initial CREATE VIEW was done as a SELECT from the database, so if the create view was quick, I thought that the select from the view would be equally quick. Is this a faulty assumption? > "DROP VIEW tempview; > DROP VIEW > > postgresql did that successfully, but when I then did > > select * from tempview: > > " > > Where the select would have been on the regular view named tempview. > > > > Thanks, > Celia McInnis > > On Wed, Mar 20, 2024 at 1:01 PM Adrian Klaver > wrote: > >> On 3/20/24 09:51, Celia McInnis wrote: >> > The view is being used in some web query software that multiple people >> > will be accessing and the contents of the view depend on what the >> person >> > is querying, so I think that temporary views or tables are a good idea. >> > I change to non-temporary views or tables (in a test version of the >> > software which is not web-crawl-able) when I'm trying to debug things, >> > and I guess I have to be careful to clean those up when I switch back >> to >> > the temporary tables/views. >> >> Why change behavior for the tests? Seems that sort of negates the value >> of the testing. >> >> Have you run EXPLAIN ANALYZE on the problem query? >> >> >> > >> > >> > >> > On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver >> > mailto:adrian.kla...@aklaver.com>> wrote: >> > >> > On 3/20/24 08:39, Celia McInnis wrote: >> > > Ok, thanks - so I guess that means that if there is both a >> > temporary and >> > > a non temporary view called "tempvie", >> > > >> > > DROP VIEW tempview; >> > > >> > > will remove the 1st tempview found, which with my path is the >> > temporary >> > > one. Is there some reason why it then took 7 minutes to select >> > from the >> > > non-temporary view tempview after I dropped the temporary view >> > tempview? >> > > >> > > I have sometimes had some very long query times when running >> query >> > > software, and maybe they are resulting from my switching between >> > > temporary and non-temporary views of the same name while >> > debugging. If >> > > so, is there something I should be doing to clean up any >> temporary >> > > messes I am creating? >> > >> > What is the purpose of the temp view over the the regular view >> process? >> > >> > How do they differ in data? >> > >> > Is all the above happening in one session? >> > >> > Have you run EXPLAIN ANALYZE on the select from the regular view? >> > >> > > >> > > Thanks, >> > > Celia McInnis >> > > >> > >> > >> > -- >> > Adrian Klaver >> > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> > >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >> -- > Adrian klaveradrian.kla...@aklaver.com > >
adding a generated column to a table?
If I have a table containing a date field, say:
create temporary table tmp1 as select now()::date as evtdate;
SELECT 1
select DATE_PART('year', evtdate)::integer as year from tmp1;
year
--
2024
(1 row)
Is there some way of doing something like the following?:
alter table tmp1 add column year integer generated always as
DATE_PART('year', evtdate)::integer STORED;
ERROR: syntax error at or near "DATE_PART"
LINE 1: ... tmp1 add column year integer generated always as DATE_PART(...
altering a column to to make it generated
Can I alter a table column to now make it generated? I tried this unsuccessfully: create temp table tmp1 as select 15::numeric(6,1) as distance,'24:30'::interval,0::numeric(7,3) as avgspd; alter table tmp1 alter column avgspd type numeric(7,3) generated always as ((3600.*distance)/EXTRACT (EPOCH FROM rdrtime::interval)) STORED; ERROR: syntax error at or near "generated" LINE 1: ... table tmp1 alter column avgspd type numeric(7,3) generated ... I think that at least I have the right bracketing this time! :-) ^
is there an immutable function to switch from date to character?
create temporary table junk as select now()::date as evtdate; alter table junk add column chardate text GENERATED ALWAYS AS (to_char(evtdate,'-Mon-DD')) STORED; ERROR: generation expression is not immutable
Re: is there an immutable function to switch from date to character?
Sorry I pasted in the wrong code, I had wanted a column with the character version of the date (ie., -Mon-DD). Steve Baldwin's hack pointed me in the right direction. Here is the example: create temporary table junk as select now()::date as evtdate; SELECT 1 alter table junk add column chardate text GENERATED ALWAYS AS (cmm_date_to_char(evtdate)) STORED; select * from junk; evtdate | chardate +- 2024-04-24 | 2024-Apr-24 (1 row) where cmm_date_to_char is defined as: create or replace function cmm_date_to_char(i_date in date) returns text immutable language sql as $$ select to _char(i_date, '-Mon-DD') $$; Thanks! On Wed, Apr 24, 2024 at 5:54 PM Tom Lane wrote: > Celia McInnis writes: > > create temporary table junk as select now()::date as evtdate; > > alter table junk add column chardate text GENERATED ALWAYS AS > > (to_char(evtdate,'-Mon-DD')) STORED; > > > ERROR: generation expression is not immutable > > Probably not; I think all the available conversion functions > respond to some combination of datestyle, lc_time, and timezone > settings. (Type date doesn't depend on timezone, but that keeps you > from using anything that shares functionality with timestamptz ... > and your to_char call promotes the date to timestamptz.) > > I find your example not terribly compelling. Why expend storage > space on such a column? > > If you're bound and determined to do it, writing a wrapper > function that's labeled immutable should work: > > =# create function mytochar(date) returns text > strict immutable parallel safe > as $$ begin return to_char($1::timestamp, '-Mon-DD'); end $$ > language plpgsql; > CREATE FUNCTION > =# alter table junk add column chardate text GENERATED ALWAYS AS > (mytochar(evtdate)) STORED; > ALTER TABLE > > It's on you to be sure that the function actually is immutable, > or at least immutable enough for your use-case. I believe my > example is pretty safe: neither datestyle nor timezone should > affect the timestamp-without-timezone variant of to_char(), > and this particular format string doesn't depend on lc_time. > > regards, tom lane >
Treating float arrays as vectors?
Hi: I would love it if there was a vector data type in postgresql along with such vector operations as addition, subtraction, scalar multiplication, cross product, dot product, normalization, length and various sorts of vector distances. So far I have been feeding my float arrays to plpython3u to do these kinds of operations. Is there a better way I can get this functionality? Celia McInnis
spiexceptions.UndefinedFile: could not open file "base/16482/681830": No such file or directory
Help - I don't know why I am getting this message, and I don't know how to
fix it. Any advice will be greatly appreciated.
Note that the file does exist:! (How do I know if it is looking under the
correct directory? Other times I have done similar temporary table
creations with no problems!):
*ls -l /zfs/postgres/postgres13/base/16482/681830-rw--- 1 postgres
postgres 122880 Sep 7 18:37 /zfs/postgres/postgres13/base/16482/681830*
I am using postgresql 13 on ubuntu 20.04, and postgresql is on a zfs
filesysem which reports no errors. I can access every table in my
database, and can run every stored procedure (plpgsql and plpython3u) on it
except the one that generates the above error..
Here is the output from my run:
*select cmm_tsv_to_tables('/zfs/EXPERIMENT_A_C_NORMAL_0_1_2/10');NOTICE:
['found_patterns.tsv', 'classification.tsv', 'mined_patterns.tsv']NOTICE:
['tmp_2021_09_08_19_49_42_354417_found_patterns',
'tmp_2021_09_08_19_49_42_354417_classification',
'tmp_2021_09_08_19_49_42_354417_mined_patterns']NOTICE: CREATE TABLE
tmp_2021_09_08_19_49_42_354417_found_patterns(Class TEXT,filename
TEXT,pattern TEXT)ERROR: spiexceptions.UndefinedFile: could not open file
"base/16482/681830": No such file or directoryLINE 1: CREATE TABLE
tmp_2021_09_08_19_49_42_354417_found_patterns(...
^QUERY: CREATE TABLE tmp_2021_09_08_19_49_42_354417_found_patterns(Class
TEXT,filename TEXT,pattern TEXT)CONTEXT: Traceback (most recent call
last): PL/Python function "cmm_tsv_to_tables", line 39, in
*plpy.execute(sql1)PL/Python function "cmm_tsv_to_tables"*
and here (and attached) is the function up to the point where the error
happens:
-
CREATE OR REPLACE FUNCTION cmm_tsv_to_tables( tsv_dir TEXT) RETURNS TEXT AS
$$
import csv
import datetime
import glob
import os
import regex
import time
ts = time.time()
prefix = "tmp_" +
regex.sub(r"\D+","_",str(datetime.datetime.fromtimestamp(time.time()).isoformat()))
+ "_"
if not os.path.exists(tsv_dir):
plpy.notice("Scout output directory " + tsv_dir + " does not exist.
Rerun this software with a directory which contains the tsv files you wish
to database.")
return ""
try:
os.chdir(tsv_dir)
except:
plpy.notice("postgresql does not have access to the directory " +
tsv_dir + " of tsv files. Exiting!")
return ""
tsv_file = glob.glob('*.tsv')
if len(tsv_file) == 0:
plpy.notice("There are no tsv files in " + tsv_dir + ". Exiting!")
return ""
plpy.notice(tsv_file)
table = [ prefix+os.path.splitext(x)[0] for x in tsv_file]
plpy.notice(table)
# create tables with text columns, some of which will later be casted to
numbers, arrays, etc.
for j in range(len(tsv_file)):
with open(tsv_file[j]) as f:
cols = f.readline().split()
colsdef = ','.join([x + ' TEXT' for x in cols])
sql1 = "CREATE TABLE {}({})".format(table[j], colsdef)
plpy.notice(sql1)
#return 'exit'
plpy.execute(sql1)
sql1 = "COPY {} FROM '{}' WITH (FORMAT 'csv', HEADER, DELIMITER E'\t',
NULL 'NULL')".format(table[j], tsv_file[j]);
plpy.notice(sql1)
plpy.execute(sql1)
return "Tables were written"
$$ LANGUAGE plpython3u;
cmm_tsv_to_tables.plpython3u
Description: Binary data
Re: spiexceptions.UndefinedFile: could not open file "base/16482/681830": No such file or directory
YES!!! The chdir caused the error! Thanks very much for this help. BTW it was worse than just "not a very good idea" to use chdir - After generating the error, I would lose the ability to see things in my database until restarting postgresql. All's okay now! On Wed, Sep 8, 2021 at 8:56 PM Thomas Munro wrote: > On Thu, Sep 9, 2021 at 9:19 AM Celia McInnis > wrote: > > Note that the file does exist:! (How do I know if it is looking under > the correct directory? Other times I have done similar temporary table > creations with no problems!): > > PostgreSQL internally uses relative paths. It's probably not a very > good idea to use 'chdir' in a procedure. >
creating a table in plpython?
Hi - Is it possible to create a table inside a plpython stored procedure? If so, can you give an example of how to do so? Many thanks, Celia McInnis
Re: creating a table in plpython?
Thanks - works like a charm. Hmmm. I tried that earlier and got errors. I
just retried and it worked fine this time. I guess I had some other problem
- It seems that sometimes I have to exit and reenter my psql session to use
a newly interpreted version of my stored procedure, so maybe that was part
of the problem...
On Wed, Jan 10, 2018 at 6:34 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Jan 10, 2018 at 4:27 PM, Celia McInnis
> wrote:
>
>> Hi - Is it possible to create a table inside a plpython stored procedure?
>> If so, can you give an example of how to do so?
>>
>>
> Haven't used pl/python myself but...
>
> https://www.postgresql.org/docs/10/static/plpython-database.html
>
> so... plpy.execute("CREATE TABLE ...") ?
>
> David J.
>
>
