Re: Control PhoneNumber Via SQL

[hamann . w]

>> Hi,
>> 
>> Sorry for asking question again.
>> 
>> I would like to know if it is possible to control the phone number in SQL
>> before inserting it to the destination DB?
>> 
>> I have a model in Django:
>> 
>> class BasePerson(TimeStampedModel):
>>  phone_number = PhoneNumberField(max_length=50, verbose_name=_(u'phone
>> number'), blank=True)
>> 
>> 
>> The data for phone number that I am migrating doesn't have country code. I
>> want to determine first if the number has country code in it, if it doesn't
>> then I will add the country code on the number before INSERTING it to the
>> destination database.
>> 
Hi, something like
insert into newtable (phone, ...)
 select case when phone ~ '^0[1-9]' then regex_replace('0', '+49', phone) else
  case when phone ~ '^00' then regex_replace('00', '+', phone) else phone end 
end, ...
 from oldtable;

Regards
Wolfgang

>> Any suggestion will be highly appreciated.
>> 
>> 
>> Thanks,
>> J
>> 

sql questions

[hamann . w]

Hi,

a) I am running some select query
select ... order by 
Now, I would like to preserver the ordering through further processing by 
adding a sequence number
Of course I can do:
create temp sequence mseq;
select xx.*, nextval('mseq') as ord from (select ... order by ) xx;
drop sequence mseq;
Is there a simpler way (avoiding the create/drop parts)

b) can a sql function return the count of affected rows of some query?
create function merge_names(int, int) returns void as
$_$
update namelinks set nid = $2 where nid = $1;
-- want the affected rows of the above query
delete from names where nid = $1
-- return result here
$_$
language sql;

Best regards
Wolfgang Hamann

Re: sql questions

[hamann . w]

>> Zitat von haman...@t-online.de:
>> 
>> > a) I am running some select query
>> > select ... order by 
>> > Now, I would like to preserver the ordering through further  
>> > processing by adding a sequence number
>> > Of course I can do:
>> > create temp sequence mseq;
>> > select xx.*, nextval('mseq') as ord from (select ... order by ) xx;
>> > drop sequence mseq;
>> > Is there a simpler way (avoiding the create/drop parts)
>> 
>> Can't you just do the ordering at the end of the processing? Maybe you  
>> need to drag along the order by columns and just dump them at the very  
>> end if applicable.
>> 


Hi,

in this specific case every search result consists of a pair of related entries 
 that are not close to each other in
ordering. So I order by first entry and use the row number to keep the second 
entry next to the first one,

BTW: the use case is scanning a database of people for duplicates. Whenever 
there are 3 or more
components in a name, the split betwwen first and last name can be ambiguous, 
and so its is common to find
both "Ludwig" "van Beethoven" and "Ludwig van" "Beethoven"

Best regards
WOlfgang

libpq.dll question

[hamann . w]

Hi,

admitting that windows is quite alien to me...
I have installed a postgresql server on linux and now want to allow windows 
clients access through a tcl gui.
Activestate tcl is installed, and when I try to
package require tdbc::postgresql
I get a complaint about libpq.dll.5 missing. The postgres install kit contains 
just a libpq.dll, and renaming it
does not seem to help

Best regards
Wolfgang Hamann

trouble writing plpgsql

[hamann . w]

Hi,

I want to make a function to parsetext and return key-value pairs
create or replace function extractinfo (text) returns table (key char[1], val 
text) 
language plpgsql as $$

I first tried
declare
xx record;
begin

xx.key = ; xx.val = ;
return next xx:

This is not possible because xx needs to know its structure before the fields 
can be assiged to.
Could I declare xx as having these fields in the first place, do Ineedto create 
a type for key, val
first?

Wolfgang Hamann 

SQL question

[hamann . w]

Hi,

I have atable with a "name" column, and I order it usually
order by regexp_match(name, 'regex1'), regexp_match(name, 'regex2')
orthe equivalent
order by (regexp_match(name, 'regex1'))[1], (regexp_match(name, 'regex2'))[2]

Is there a wayto create a function so the statement could read
select ... order by myfunc(name)

Best regards
Wolfgang

conditionally terminate psql script

[hamann . w]

Hi,

is there a way to stop execution of a psql script if a select returns some rows 
(or no rows)
The idea is to add a safety check on data, specifically to select all new rows 
that would conflict
on a bulk insert, show them and stop

Best regards
Wolfgang

Re: conditionally terminate psql script

[hamann . w]

Hi,

many thanks -- too bad I am still using 9.3

Best regards
Wolfgang

>> Hi
>> >> po 17. 12. 2018 v 13:14 odesílatel  napsal:
>> >> >
>> >
>> > Hi,
>> >
>> > is there a way to stop execution of a psql script if a select returns some
>> > rows (or no rows)
>> > The idea is to add a safety check on data, specifically to select all new
>> > rows that would conflict
>> > on a bulk insert, show them and stop
>> >
>> >> you need psql from PostgreSQL 10 and higher
>> >> there is a \if statement
>> >> Regards
>> >> Pavel
>> >> >
>> > Best regards
>> > Wolfgang
>> >
>> >
>> >
>> 

Re: conditionally terminate psql script

[hamann . w]

Hi,

many thanks, I will give it a try tomorrow

Best regards
Wolfgang

>> >> On 17.12.2018 16:07, haman...@t-online.de wrote:
>> > Hi, many thanks -- too bad I am still using 9.3
>> >> In this case you can try ON_ERROR_STOP psql variable.
>> Something like this:
>> >> \set ON_ERROR_STOP on
>> >> do $$
>> declare
>>      total bigint;
>> begin
>>      select count(*) into total from pg_class where 1=1;
>>      if total = 0 then
>>      raise exception 'Nothing found.';
>>      end if;
>> >>      raise notice '% records found.', total;
>> end;
>> $$ language plpgsql;
>> >> \echo Continue execution...
>> >> -
>> Pavel Luzanov
>> Postgres Professional: http://www.postgrespro.com
>> The Russian Postgres Company
>> >> >> 

data definition within plpgsql

[hamann . w]

Hi,

I tried this code (to be inserted within a larger psql script)

do $_$
declare
next int;
begin
select max(id) + 1 into next from items;
execute 'create temp sequence tmp_ids start $1' using next;
end
$_$ language plpgsql;

but that reports a syntax error near $1.
What is the proper way of doing that?

Best regards
Wolfgang Hamann

Re: How to use full-text search URL parser to filter query results by domain name?

[hamann . w]

>> I am trying to understand how to use the full-text search parser for
>> URLS and hostnames to filter results from a text field containing URLS
>> based on domain, and also how to index text columns for fast
>> lookup/matching based on domain.
>> >> I have a PostgreSQL database containing documents and links downloaded
>> by a web crawler, with the following tables:
>> >>     pages
>> >>     --
>> >>     id:  Integer (primary key)
>> >>     url: String  (unique)
>> >>     title:   String
>> >>     text:    String
>> >>     html:    String
>> >>     last_visit:  DateTime
>> >>     word_pos:    TSVECTOR
>> >>     >> >>     links
>> >>     --
>> >>     id Integer (primary key)
>> >>     source:    String
>> >>     target:    String  >> >>     link_text: String
>> >>     UNIQUE(source,target)
>> >>     >> >>     crawls
>> >>     -
>> >>     id: Integer (primary key)
>> >>     query:  String
>> >>     >> >>     crawl_results
>> >>     -
>> >>     id:   Integer (primary key)
>> >>     score:    Integer (constraint 0<=score<=1)
>> >>     crawl_id: Integer (foreign key, crawls.id)
>> >>     page_id:  Integer (foreign key, pages.id)
>> >> >> The `source` and `target` fields in the `links` table contain URLs. I 
>> >> >> am
>> running the following query to extract scored links from the top-ranking
>> search results, for pages that haven't been fetched yet:
>> >>     WITH top_results AS >> >>     (SELECT page_id, score FROM 
>> >> crawl_results >> >>     WHERE crawl_id=$1 >> >>     ORDER 
>> >> BY score LIMIT 100)
>> >>     SELECT top_results.score, l.target
>> >>     FROM top_results >> >>     JOIN pages p ON 
>> >> top_results.page_id=p.id
>> >>     JOIN links l on p.url=l.source >> >>     WHERE NOT EXISTS 
>> >> (SELECT pp.id FROM pages pp WHERE l.target=pp.url)
>> >> >> However, *I would like to filter these results so that only one row is
>> returned for a given domain (the one with the lowest score)*. So for
>> instance, if I get (0.3, 'http://www.foo.com/bar') and (0.8,
>> 'http://www.foo.com/zor'), I only want the first because it has same
>> domain `foo.com` and has the lower score.
>> >> I was able to find documentation for the builtin full text search
>> parsers ,
>> which can parse URLS and extract the hostname. For instance, I can
>> extract the hostname from a URL as follows:
>> 
Hi,

I have no real idea about solving the complete problem, and would probably try
something with a temp table first.
For extracting the hostname from a url you could use

select regex_replace('https?://(.*=)/.*', '\\1', url)

instead of the fulltext parser

Best regards
Wolfgang

unicode match normal forms

[hamann . w]

Hi,

in unicode letter ä exists in two versions - linux and windows use a composite 
whereas macos prefers
the decomposed form. Is there any way to make a semi-exact match that accepts 
both variants?
This question  is not about fulltext but about matching filenames across a 
network - I wish to avoid two equally-looking
filenames.

Regards
Wolfgang

SQL questiom

[hamann . w]

Hi,

I am using a query pretty often that looks like
SELECT <> WHERE <> AND
<>

Is there a way (with sql or plpgsql)  to convert that into
SELECT myquery('<>')

Kind regards
Wolfgang Hamann