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+'))
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_matches
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,
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
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
database.
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_interval.sql'
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
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 **
ERROR: syntax
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 to
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 ', '
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,
My question and your answer have now become part of a blog entry at my
site: http://solumslekt.org/blog/. Thank you again.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
---(end of
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
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
On Thursday 09 March 2006 14:35, Klay Martens wrote:
p class=MsoNormalspan lang=EN-ZAHi all.o:p/o:p/span/p
p class=MsoNormalspan lang=EN-ZAI am new to postgres, so I am
still learning the basics.o:p/o:p/span/p
p class=MsoNormalspan lang=EN-ZAIn Sequel Server, one can set
up a function to
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 behaviour
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 referenced
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, just
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;
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 out
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
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
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:
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 TABLE relation_citations
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 integer references
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
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
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 trigger:
CREATE
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
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 here,
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
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 |
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
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_attribute from
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
http://solumslekt.org/forays/blue.php.
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_fk = P.person_id
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:
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
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:
slekta= update
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
40 matches
Mail list logo
