Re: [SQL] Plpgsql: Assign regular expression match to variable

On Tuesday 1. September 2009, Ian Barwick wrote: >This seems to do what you want: > > my_int := (REGEXP_MATCHES(txt, E'^#(\\d+)'))[1]; Great! I had no idea that REGEXP_MATCHES() could do that kind of stuff. pgslekt=> select (REGEXP_MATCHES('#42 blabla', E'^#(\\d+)')) [1]::integer; regexp_match

[SQL] Plpgsql: Assign regular expression match to variable

In Plpgsql, I've got this problem of how to assign an integer extracted from a regex to a variable. My approach so far feels kludgy: -- extract ^#(\d+) from txt IF txt SIMILAR TO E'#\\d+%' THEN my_int := SUBSTR(SUBSTRING(txt, E'#\\d+'), 2, LENGTH(SUBSTRING(txt, E'#\\d+')) -1)::INTEGER

Re: [SQL] Multiple return values and assignment

On Monday 27. April 2009, Jasen Betts wrote: >SELECT * FROM get_sort(par_id, srt, txt) INTO srt,txt; Thank you very much! That saved me from one composite variable declaration and two superfluous lines of code. I've settled for SELECT number, string FROM get_sort(par_id, srt, txt) INTO srt, txt

[SQL] Multiple return values and assignment

I've got a function that returns both an integer and a string as a user-defined composite type int_text: -- CREATE TYPE int_text AS (number INTEGER, string TEXT); Basically, the function does some heuristics to extract a sort order number from a text, and conditionally modify the text: CREATE

Re: [SQL] Convert text from UTF8 to ASCII

On Wednesday 11. March 2009, Paul Dam wrote: >Hoi, > > > >I store content of an .txt file in a text column in the database. > >server_encoding is UTF8. > > > >If the .txt file is in ASCII this is correctly stored in the database. > >If the .txt file is in UTF8 this is NOT correctly stored in the >

Re: [SQL] Creating a function with single quotes

On Friday 20. February 2009, Adrian Klaver wrote: >On Friday 20 February 2009 6:29:43 am Leif B. Kristensen wrote: >> About twenty years ago I wrote a lot of Turbo Pascal code, and IIRC >> semicolon after an END was allowed but considered bad style. The rules concerning ENDs an

Re: [SQL] Creating a function with single quotes

On Friday 20. February 2009, Adrian Klaver wrote: >Actually you need both semicolons. One after the RETURN statement and > one after the END statement >See below for full details: >http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html I see the documentation, but empirically you d

Re: [SQL] Creating a function with single quotes

On Friday 20. February 2009, Shawn Tayler wrote: >Hello Jasen and the List, > >I tried the $$ quote suggestion: > >create function f_csd_interval(integer) returns interval as >$$ >BEGIN >RETURN $1 * interval '1 msec' >END; >$$ >LANGUAGE 'plpgsql'; > >Here is what I got: > >edacs=# \i 'f_csd_interva

Re: [SQL] EXISTS

On Saturday 11. October 2008, Denis Woodbury wrote: >Hi, > >I would like to know if this this type of statement can be used in >Postgresql > >IF NOT EXISTS (SELECT 1 FROM Table WHERE col1 = 'mystring' ) >BEGIN >... >END > >If it can, any idea why I get this error. >** Error ** > >ER

Re: [SQL] php to pgsql question

On Tuesday 4. March 2008, li ethan wrote: >HI guys! >I've been encounting a problem when I configured PHP to support >Postgres.I think may be someone in here can solve my problem. >Postgres version is 8.1.4,and the install path is > /usr/local/pgsql, PHP is 4.4.2. >I use this parameter

Re: [SQL] Concatenation through SQL

On Friday 21. December 2007, Niklas Johansson wrote: >select array_to_string(array[given, patronym, toponym], ' ') from >persons where person_id=57; > >Notice the use of the array[] constructor, instead of the array() >constructor, which must be fed a subquery which returns only one > column. Aah

Re: [SQL] Concatenation through SQL

On Friday 21. December 2007, Philippe Lang wrote: >(SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id > = appointments.id) AS employees >FROM appointments >-- > >... where CONCAT suggest we want to concatenate the variable inside, >with the separator

Re: [SQL] Finding multiple events of the same kind

My question and your answer have now become part of a blog entry at my site: . Thank you again. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--

Re: [SQL] Finding multiple events of the same kind

On Sunday 11. June 2006 20:36, Richard Broersma Jr wrote: >Also, you could create a unique column constraint that would prevent > multiply instances of the same person in the participants table. I have considered that as well. But as my front end main view looks like a "structured document" in a

Re: [SQL] Finding multiple events of the same kind

On Sunday 11. June 2006 15:27, Frank Bax wrote: >SELECT participants.person_fk, count(participants.person_fk) FROM > events, participants >    WHERE events.event_id = participants.event_fk >         AND events.tag_fk in (2,62,1035) >    GROUP BY participants.person_fk HAVING > count(participants.pe

[SQL] Finding multiple events of the same kind

I've got two tables: CREATE TABLE events ( event_idINTEGER PRIMARY KEY, tag_fk INTEGER NOT NULL REFERENCES tags (tag_id), place_fkINTEGER NOT NULL REFERENCES places (place_id), event_date CHAR(18) NOT NULL DEFAULT '31', sort_date DATE NOT NULL DE

Re: [SQL]

On Thursday 09 March 2006 14:35, Klay Martens wrote: >Hi all. > > >I am new to postgres, so I am > still learning the basics. > > >In Sequel Server, one can set > up a function to return a table eg: > > >  > > >CREATE FUNCTION > [dbo].[AuthCodes] (@CLIENTID INT)  > > > >RETURNS @AuthCodes > TABLE

Re: [SQL] Error calling self-made plpgsql function "function XYZ(bigint) does not exist"

On Thursday 19 January 2006 14:06, Juris wrote: >Argh... big thanks.. did not know what pgAdmin/PG have any >case-sensitive issues with functions... > >Also for fields it is relative... without dbl-quotes i could not query >anything (i am using "MySuperField"-like field names) That is usual behavi

Re: [SQL] FOREIGN KEYs ... I think ...

On Thursday 05 January 2006 04:58, [EMAIL PROTECTED] wrote: >That's not what foreign keys do. The only thing a foreign key > provides is a guarantee that if any records in B (the referencing > table) still reference a record in table A (the referenced table) > then you cannot delete that reference

Re: [SQL] Error from trigger

On Thursday 08 December 2005 05:11, Tom Lane wrote: >Just starting a fresh session should make the problem go away, or if >that's not practical update the function definition using ALTER > FUNCTION or CREATE OR REPLACE FUNCTION. (You don't need to actually > *change* anything about the function, j

Re: [SQL] Error from trigger

On Thursday 08 December 2005 00:23, Tom Lane wrote: >Is there a reason you don't just mark the FK reference as ON DELETE >CASCADE, rather than using a handwritten trigger? I could have done that, of course. I'm still a little shaky on "best practice" with these things. Besides, I haven't found ou

[SQL] Error from trigger

Hello, I have a trigger that will delete records referring to an "events" table upon deletion. I have used it without problems for a number of times: CREATE OR REPLACE FUNCTION delete_event_cascade() RETURNS TRIGGER AS $$ BEGIN DELETE FROM event_citations WHERE event_fk = OLD.event_id; DE

Re: [SQL] Strange bug

On Tuesday 29 November 2005 17:01, Leif B. Kristensen wrote: >Is there an easy and non-disruptive way to do this? For the record, I just did the following: pgslekt=> create table rel_cits ( pgslekt(> relation_fk integer references relations (relation_id), pgslekt(> source_fk intege

Re: [SQL] Strange bug

On Tuesday 29 November 2005 15:52, Leif B. Kristensen wrote: >Uh-oh. That's my first 'gotcha' in PostgreSQL. > >I added the following constraints: I probably should drop both the inheritance and the citation_id altogether, and operate with two separate tables: CREATE

Re: [SQL] Strange bug

On Tuesday 29 November 2005 15:43, A. Kretschmer wrote: >> I got an error when I transferred the data to my Web database >> running MySQL: > >MySQL is a other RDBMS. You can't expect that all features from >PostgreSQL are working with MySQL. > >> ERROR 1062 at line 19839 in file: 'ss_relation_citat

Re: [SQL] Strange bug

On Tuesday 29 November 2005 15:37, Tom Lane wrote: >relation_citations doesn't have a primary key. See >http://www.postgresql.org/docs/8.1/static/ddl-inherit.html >particularly the "caveats" section. Uh-oh. That's my first 'gotcha' in PostgreSQL. I added the following constraints: ALTER TABLE r

[SQL] Strange bug

I just noticed that I accidentally got a duplicate id. My definitions are here: CREATE TABLE citations ( citation_id INTEGER PRIMARY KEY, source_fk INTEGER REFERENCES sources (source_id) ); CREATE TABLE relation_citations ( relation_fk INTEGER REFERENCES rela

[SQL] Index wonder

I just wanted to share my revelation on how an index can do wonders for a query: pgslekt=> explain select child_fk, get_coparent(570,child_fk), get_pbdate(child_fk) as pbd from relations where parent_fk = 570 order by pbd; QUERY PLAN --

Re: [SQL] Triggers

On Tuesday 22 November 2005 18:07, Achilleus Mantzios wrote: >O Leif B. Kristensen ?? Nov 22, 2005 : >> What am I missing? > >apparently some forgotten process_last_edited() function. Yes -- an earlier attempt at the same thing ... I finally managed to create my first t

Re: [SQL] Triggers

On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote: >O Leif B. Kristensen έγραψε στις Nov 22, 2005 : >> I'm trying to understand triggers. I have read the documentation in >> the manual as well as the few pages in the Douglas book about the >> subject, but I do

[SQL] Triggers

I'm trying to understand triggers. I have read the documentation in the manual as well as the few pages in the Douglas book about the subject, but I don't see how to implement a trigger that simply updates a 'last_edit' date field in my 'persons' table whenever I do an insert or update into my

Re: [SQL] Use of partial index

On Wednesday 05 October 2005 18:49, you wrote: [Leif] > > Now, here's an "explain select": > > > > pgslekt=> explain select event_date, place from principals where > > person=2 and tag_type=2; > >                                        QUERY PLAN > > --

Re: [SQL] Use of partial index

On Wednesday 05 October 2005 18:44, you wrote: > As I understand it, partial indices are generally useful when you > only want to index a range of values, or if the select condition is > on a different field from the one being indexed (eg: ON foo (a) WHERE > b IS NOT NULL). > > I am just guessing h

[SQL] Use of partial index

I'm a little confused about partial indexes. I have a couple of tables, like this: CREATE TABLE events ( event_idINTEGER PRIMARY KEY, tag_type_fk INTEGER REFERENCES tag_types (tag_type_id), place_fkINTEGER REFERENCES places (place_id), event_date CHAR(18) NOT NULL DEFAULT

Re: [SQL] Help with a view

On Thursday 22 September 2005 20:03, Dmitri Bichko wrote: > SELECT * FROM principals WHERE event = 15821 AND person != 2? Sure, that's a concise answer to what I actually wrote, but it wasn't exactly what I intended :) Basically, what I've got is the first person and the tag_type. I can do it

[SQL] Help with a view

I've created a view 'principals' that gives me this output: pgslekt=> select * from principals where event=15821; person | event | place | sort_date | tag_type +---+---++-- 2 | 15821 | 1152 | 1999-09-17 |4 3 | 15821 | 1152 | 1999-09-17 |

Re: [SQL] How to add column from old_table to new_table?

On Wednesday 21 September 2005 14:36, Joost Kraaijeveld wrote: > Hi, > > I have an old_table with two columns: "id" and "old_attribute". I > have new table with the columns "id" and "new_attribute". > > old_table and new_table contain exactly the same id's. Now I want to > copy all the old_attribut

Re: [SQL] Need help with 'unique parents' constraint

On Sunday 11 September 2005 16:04, Greg Sabino Mullane wrote: > Not just old-fashioned, it's the biological law! (among homo sapiens > anyway). I'd approach this with a trigger, as you can do complex > checks and get back nice customized error messages. A sample script > follows. Hard to tell with

Re: [SQL] Need help with 'unique parents' constraint

On Sunday 11 September 2005 14:24, Leif B. Kristensen wrote: > ALTER TABLE relations ADD CONSTRAINT non_unique_father > CHECK (NOT EXISTS > (SELECT persons.person_id, relations.parent_fk > FROM persons AS P, relations AS R > WHERE R.parent_f

[SQL] Need help with 'unique parents' constraint

This message has also been posted to comp.databases. I've got a problem that I can't quite wrap my head around, about adding a constraint to my PostgreSQL genealogy database. If somebody are interested, I've written some thoughts on the general design at . I

Re: [SQL] "Flattening" query result into columns

On Monday 21 March 2005 22:57, Thomas Borg Salling wrote: > I am looking for a way to "flatten" a query result, so that rows are > "transposed" into columns, just as asked here for oracle: > > Is there any way to do this with pgsql  ? Just to help out the guys, here's a working link:

Re: [SQL] update with subselect (long)

On Thursday 17 March 2005 15:01, Stephan Szabo wrote: > The above needs some work. The below should be acceptable to the > system. > > update name_parts set name_part_type=5 from (select name_id from > name_parts where name_part_type=6) as gpt_type where > name_parts.name_id=gpt_type.name_id and

Re: [SQL] update with subselect (long)

On Thursday 17 March 2005 14:07, Leif B. Kristensen wrote: > slekta=> update name_parts set name_part_type=6 where > name_part_type=3; This message was sent a little prematurely while I was editing a similar posting to comp.databases. The cited line is erroneous and should read:

[SQL] update with subselect (long)

Hello all, I'm working with a genealogy database where I try to implement a somewhat unconventional model for names. The purpose is to allow different naming styles, especially the old Norwegian naming style with Given name/Patronym/Toponym instead of the Given/Patronym style that appears as Go