Re: [SQL] exclusion constraint for ranges of IP

[Misa Simic]

Hi Herouth,

I think you are right about exclusion...

If you are getting 'string' I think then command would be:

INSERT INTO customer_ip_range(cutomer_id, ip4r) VALUES('customeridstring',
ip4r('iprangestring'))

Kind Regards,

Misa

2011/8/22 Herouth Maoz 

>
> On 22/08/2011, at 01:19, Harald Fuchs wrote:
>
> > In article ,
> > Herouth Maoz  writes:
> >
> >> Hi,
> >> I'm designing a new database. One of the table contains allowed IP
> ranges for a customer (Fields: customer_id, from_ip, to_ip) which is
> intended to check - if  an incoming connection's originating IP number falls
> within the range, it is identified as a particular customer.
> >
> >> Naturally, I'd like to have constraints on the table that prevent
> entering of ip ranges that overlap. Is there a way to do that with exclusion
> constraints? Or do I have to define a new type for this?
> >
> > This "new type" already exists: ip4r, which can be found in pgfoundry.
> > With it you can do
> >
> > CREATE TABLE mytbl (
> >  iprange ip4r NOT NULL,
> >  ...,
> >  CONSTRAINT range_check CHECK ((NOT overlap(iprange)))
> > );
>
>
> Thank you.
>
> I assume you can't use a CHECK constraint for between-rows constraints.
> Wouldn't this  be
>
> CONSTRAINT EXCLUDE ( iprange WITH && )
>
> ?
>
> Basically, though, I'm not too happy about using compound types - that's
> why I asked if I have to. I'm not sure what my application will have to send
> and what it will receive when querying a compound type. I use PHP/ZF. I have
> just now posted a question on the pgsql-php list about this. I suspect I'll
> be getting a string which I'll have to parse, which would make the
> application more complicated to read and understand.
>
> Herouth
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] Subselects to Joins? Or: how to design phone calls database

[Misa Simic]

I think its definitely better to split phone number in calls table on 2
or even 3 parts... (Country prefix, carrier/area prefix, number)

Though maybe better design would be 3th table with full number as pk:
PhoneNumbers (number, country prefix, optionally carrier/area prefix,
rest of number)

Then you can join calls to phonenumbers on full number string then join
countries on country prefix...

Kind Regards,

Misa

Sent from my Windows Phone From: Mario Splivalo
Sent: 10 December 2011 23:27
To: pgsql-sql@postgresql.org
Subject: [SQL] Subselects to Joins? Or: how to design phone calls
database
I have a table called 'calls' which holds 'call detail records'. Let's
assume the table looks like this:

CREATE TABLE cdr (
call_id serial,
phone_number text
);

And I have a table with country call prefixes, that looks like this:

CREATE TABLE prefixes (
prefix text,
country text
);

And now some test data:

INSERT INTO prefixes VALUES ('1', 'USA');
INSERT INTO prefixes VALUES ('44', 'UK');
INSERT INTO prefixes VALUES ('385', 'Croatia');
INSERT INTO prefixes VALUES ('387', 'Bosnia');
INSERT INTO prefixes VALUES ('64', 'New Zeland');
INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile');
INSERT INTO calls VALUES (1, '11952134451');
INSERT INTO calls VALUES (2, '448789921342');
INSERT INTO calls VALUES (3, '385914242232');
INSERT INTO calls VALUES (4, '385914242232');
INSERT INTO calls VALUES (5, '645122231241');
INSERT INTO calls VALUES (6, '444122523421');
INSERT INTO calls VALUES (7, '64212125452');
INSERT INTO calls VALUES (8, '1837371211');
INSERT INTO calls VALUES (9, '11952134451');
INSERT INTO calls VALUES (10, '448789921342');
INSERT INTO calls VALUES (11, '385914242232');
INSERT INTO calls VALUES (12, '385914242232');
INSERT INTO calls VALUES (13, '645122231241');
INSERT INTO calls VALUES (14, '4441232523421');
INSERT INTO calls VALUES (15, '64112125452');
INSERT INTO calls VALUES (16, '1837371211');


Now, if I want to have a 'join' between those two tables, here is what I
am doing right now:

SELECT
call_id,
phone_number,
(SELECT
country
FROM
prefixes
WHERE   
calls.phone_number LIKE prefix || '%'
ORDER BY
length(prefix) DESC LIMIT 1
) AS country
FROM calls;


Is there a way I could use join here? I can do something like:

SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix || '%'

but I'd get duplicate rows there (for instance, for New Zeland calls,
from my test data).

Or should I add 'prefix' field to the calls table, and then do a inner
join with prefixes table?

Mario

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] using a generated series in function

[Misa Simic]

It is not totally clear to me what are u trying to do... But in second
query it seems there is missing "from"

It is as

SELECT week-date::date AS week-date WHERE week-date in (subquery which
have from)

So week-date column in main query does not exist..

Sent from my Windows Phone From: John Fabiani
Sent: 16 December 2011 05:16
To: pgsql-sql@postgresql.org
Subject: [SQL] using a generated series in function
Hi,

I am attempting (without success) use the generated series of dates that come
from:
select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
week_date from generate_series(0,84,7) i

in a function.
select function_name(integer, date);  -- function returns a numeric

This does NOT work:
select (function_name(303, week_date::date)) as week_date where week_date in
(select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
week_date from generate_series(0,84,7) i )

The error is:
ERROR:  column "week_date" does not exist
LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...

I hope I can do this?  What am I doing wrong?
Johnf




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] using a generated series in function

[Misa Simic]

Hi John,

Well, maybe the best would be to say on english what you want to achieve...

>From SQL code in your mail - it is not clear ( at least to me...)

but: SELECT now() - it will just execute function ant there is not possible
to say WHERE in that...

and like you said:

*select function_name(integer, date);  -- function returns a numeric*
*
*
it works - there is no place for WHERE...

If the query have WHERE - then it also at leasy must have FROM clausule...

Kind Regards,

Misa



2011/12/16 John Fabiani 

> Actually what would the "from" be - this could be a newbie issue here?
> Neither statement requires a "from" because neither of the statements uses
> a
> table - I think!  I'll try to add one but the first part is a function
> like a
> any other function.  What is the "from" when you do:
> "select now()"  - really I don't know!
>
> The second part is tricky because I don't really understand it.  Howerver,
> I
> have used it several times (got it off the web somewhere) but only in a
> "for
> loop".  If I just run it by it's self it generates a table of dates.
> Therefore, I have always thought of it as a function.  Again, like "select
> now()"
>
> So I know this must sound like I'm sort of idiot - just never considered
> the
> second half (the part that provides the dates) anything other than a
> postgres
> function.
>
> Johnf
>
>
> On Friday, December 16, 2011 01:30:53 AM Misa Simic wrote:
> > It is not totally clear to me what are u trying to do... But in second
> > query it seems there is missing "from"
> >
> > It is as
> >
> > SELECT week-date::date AS week-date WHERE week-date in (subquery which
> > have from)
> >
> > So week-date column in main query does not exist..
> >
> > Sent from my Windows Phone From: John Fabiani
> > Sent: 16 December 2011 05:16
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] using a generated series in function
> > Hi,
> >
> > I am attempting (without success) use the generated series of dates that
> > come from:
> > select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
> > week_date from generate_series(0,84,7) i
> >
> > in a function.
> > select function_name(integer, date);  -- function returns a numeric
> >
> > This does NOT work:
> > select (function_name(303, week_date::date)) as week_date where
> week_date in
> > (select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6))
> as
> > week_date from generate_series(0,84,7) i )
> >
> > The error is:
> > ERROR:  column "week_date" does not exist
> > LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...
> >
> > I hope I can do this?  What am I doing wrong?
> > Johnf
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] using a generated series in function

[Misa Simic]

That is good - that you solved it...

Well - in from it does not need to be just from table...

it needs to be some set of rows... is it Table or function (SELECT i FROM
generate_series(0, 84, 7) i - is actually from function...) or from View...

SELECT * FROM (SELECT * FROM Table1) as InlineView

Also works becouse of subquery also returns some set of rows...


Kind Regrads,

Misa

2011/12/16 John Fabiani 

> I have solved my problem.  But this still does not explain the idea of
> "from"
>
> select foo.week_date, xchromasun._chromasun_getqtyordered(303,
> foo.week_date)
> as week_qty from
>  (select ((date_trunc('week', '2011-11-20'::date )::date) + (i+6)) as
> week_date from generate_series(0,84,7)
>  i ) as foo
>
> The above works!
>
> Johnf
> On Friday, December 16, 2011 02:46:18 AM John Fabiani wrote:
> > Actually what would the "from" be - this could be a newbie issue here?
> > Neither statement requires a "from" because neither of the statements
> uses a
> > table - I think!  I'll try to add one but the first part is a function
> like
> > a any other function.  What is the "from" when you do:
> > "select now()"  - really I don't know!
> >
> > The second part is tricky because I don't really understand it.
>  Howerver, I
> > have used it several times (got it off the web somewhere) but only in a
> > "for loop".  If I just run it by it's self it generates a table of dates.
> > Therefore, I have always thought of it as a function.  Again, like
> "select
> > now()"
> >
> > So I know this must sound like I'm sort of idiot - just never considered
> the
> > second half (the part that provides the dates) anything other than a
> > postgres function.
> >
> > Johnf
> >
> > On Friday, December 16, 2011 01:30:53 AM Misa Simic wrote:
> > > It is not totally clear to me what are u trying to do... But in second
> > > query it seems there is missing "from"
> > >
> > > It is as
> > >
> > > SELECT week-date::date AS week-date WHERE week-date in (subquery which
> > > have from)
> > >
> > > So week-date column in main query does not exist..
> > >
> > > Sent from my Windows Phone From: John Fabiani
> > > Sent: 16 December 2011 05:16
> > > To: pgsql-sql@postgresql.org
> > > Subject: [SQL] using a generated series in function
> > > Hi,
> > >
> > > I am attempting (without success) use the generated series of dates
> that
> > > come from:
> > > select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6))
> > > as
> > > week_date from generate_series(0,84,7) i
> > >
> > > in a function.
> > > select function_name(integer, date);  -- function returns a numeric
> > >
> > > This does NOT work:
> > > select (function_name(303, week_date::date)) as week_date where
> > > week_date in (select (cast(date_trunc('week', '2011-11-20'::date ) as
> > > date) + (i+6)) as week_date from generate_series(0,84,7) i )
> > >
> > > The error is:
> > > ERROR:  column "week_date" does not exist
> > > LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...
> > >
> > > I hope I can do this?  What am I doing wrong?
> > > Johnf
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] Current transaction is aborted, commands ignored until end of transaction block

[Misa Simic]

"Quite a few other RDBMS will give
you the error but will also allow you to continue on your merry way (and
not loose everything you've done up to that point).

Why-o-why have the PostgreSQL developers decided to do it this way...?"

I don't know these other rdbms, but it sounds strange to have
transaction and not rollback if something is wrong...

Option in db i don't think is good generic solution because that
business rule is dynamic... In some case user wants to import
everything what is ok.. And then manually fix errors, but in some not
simply they want all or nothing...

so manually entering and import are two different processes...

Our solution for partial import case is to import all data to staging
table without constraint... Validate data... Import valid... Not valid
show to user so they can fix what is wrong etc...

Kind Regards,

Misa

Sent from my Windows Phone
From: Jan Bakuwel
Sent: 30/12/2011 23:52
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Current transaction is aborted, commands ignored
until end of transaction block
Hi Leif,

On 30/12/11 22:44, Leif Biberg Kristensen wrote:
>  Fredag 30. desember 2011 09.43.38 skrev Jan Bakuwel :
>
>> Would be nice to have an option in PostgreSQL something along the lines
>> of:  'abort-transaction-on-constraint-violation = false'
> That option is called MySQL with MyISAM tables.
>
> Seriously, if the user encounters a constraint violation, that is IMO a
> symptom of bad design. Such conditions should be checked and caught _before_
> the transaction begins.

Really?

One of my detail tables here is a list of codes. The design currently is
so that you are not allowed to add two identical codes in that table for
a particular related master record, ie. if you try it raises a
constraint violation (duplicate key). Users try anyway (you know those
pesky users doing things they're not supposed to do).

Why would that a bad design?

I simply want to tell the user: sorry you can't do this because it
violates a constraint (duplicate key).

Sometimes they try to delete something that has other records referring
to it and the database design is so that it won't cascade delete (for
various reasons). In that case I want to tell them: sorry you can't do
this because there are related records.

In a well designed system, you'd have those constraints at the database
level not the application level and use exception handling to deal with
these, not write tests to find out the possible error conditions
beforehand. Of course it's possible to write code around all of this
(and I'm starting to realise that is what I might have to do) but I
consider that bad design.

I don't claim to know all other RDBMS but I think PostgreSQL might be
one of the few (or only one) that considers a constraint violation
something really really really serious... so serious that the
transaction will have to be aborted. Quite a few other RDBMS will give
you the error but will also allow you to continue on your merry way (and
not loose everything you've done up to that point).

Why-o-why have the PostgreSQL developers decided to do it this way...?

regards,
Jan

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] ignore unique violation OR check row exists

[Misa Simic]

If exists is better, though insert line by line and check if exists may
be very slow...

The best would be if you can use copy command from csv to staging table
(without constraints) and then

Insert to live from stage where stage constraint column not exist in
live...

Kind Regards,

Misa

Sent from my Windows Phone
From: rverghese
Sent: 03/01/2012 21:55
To: pgsql-sql@postgresql.org
Subject: [SQL] ignore unique violation OR check row exists
I want to insert a bunch of records and not do anything if the record already
exists. So the 2 options I considered are 1) check if row exists or insert
and 2) ignore the unique violation on insert if row exists.
Any opinions on whether it is faster to INSERT and then catch the UNIQUE
VIOLATION exception and ignore it in plpgsql  versus check if row exists and
INSERT if it doesn't.
I can't seem to ignore the UNIQUE VIOLATION exception via php, since it is a
plpgsql command, so if I have to do the check and insert, alternatively i
have a function that tries to insert and then ignores the violation. I was
wondering if one way was better than the other.
Thanks

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/ignore-unique-violation-OR-check-row-exists-tp5117916p5117916.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] ignore unique violation OR check row exists

[Misa Simic]

Well, idea is to make process faster as possible... And usualy staging
table does not have any constrains so can't violates...

When we want to import banch of data... Process when we taking row by
row from source, validate it, if valid insert to some table could be
very slow...

Much faster is when we work with sets..

•import all records to some table without constrains (staging table).
And best would be if we can use COPY command instead of insert...

•inert into liveTable select all valid records from stagingTable

Of course it is just in case when we want to import what is ok... In
case all or nothing - import direct to liveTable works fine...

Sent from my Windows Phone
From: Jasen Betts
Sent: 04/01/2012 10:02
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] ignore unique violation OR check row exists
On 2012-01-03, Misa Simic  wrote:
> If exists is better, though insert line by line and check if exists may
> be very slow...
>
> The best would be if you can use copy command from csv to staging table
> (without constraints) and then
>
> Insert to live from stage where stage constraint column not exist in
> live...
>

Its a good idea to check that the staging table doesn't
conflict with itself, before tryign to insert it.

-- 
⚂⚃ 100% natural


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Call function with dynamic schema name

[Misa Simic]

You could take a look on EXECUTE command in plpgsql...

Depends on concrete problem but should be very careful with dynamic SQL
because of SQL injection potential risk...
Kind Regards,

Misa

Sent from my Windows Phone
From: IlGenna
Sent: 15/01/2012 18:29
To: pgsql-sql@postgresql.org
Subject: [SQL] Call function with dynamic schema name
Hi to everyone,
I would like to use in my function (plpgsql or sql) dynamic schema name to
execute query or to call other functions.

For exemple in oracle is possible to excute query in this manner:


SELECT * FROM &&SCHEMA_NAME..TABLE_NAME;

Where I think &&SCHEMA_NAME. is a sessione variable.

I found tath I can use dynamic SQL like this:

execute 'select * from ' || schema_name || '.table_name';


However, I would like to know if exist any other system to use dynamic
schema name more similiar to Oracle. Another pl language is also ok.


Thank you very much.


Alessio

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146721.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] sql query problem

[Misa Simic]

It seems question is not clear...

I could not determine what should be in column Attended, and based on
what should define passed/failed

But quick tip would be

SELECT name, CASE status WHEN 1 THEN 'Passed' ELSE 'Failed' END FROM
UserTable INNER JOIN result ON UserTable.id = result.user_id

Sent from my Windows Phone
From: Alok Thakur
Sent: 15/01/2012 22:08
To: pgsql-sql@postgresql.org
Subject: [SQL] sql query problem
Dear All,

I have two tables one contains details of user and other contains
result. The details are:
1. UserTable - id, name, phone
2. result - id, question_id, user_id, status (0 or 1)

I want the list like this:
User Id   Name   Attended   Failed   Passed

but i could not find the way to do this.

Please help

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] SELECT 1st field

[Misa Simic]

When you select from function I think column name is the same as
function name. So if function name is func query would be:

SELECT func AS id FROM func(5);



Sent from my Windows Phone
From: Jan Bakuwel
Sent: 15/05/2012 08:02
To: pgsql-sql@postgresql.org
Subject: [SQL] SELECT 1st field
Hi,

I've spend some time checking the documentation but haven't been able to
find what I'm looking for.
I've got a function that returns a set of integers and a view that
selects from the function.
What I need is the ability to name the column in the view, ie.

create function func(i int) returns setof integer as $$
...
...code
...
$$ language plpythonu volatile;

create view v as select 1 as "id" from func(5);


In other words I'd like to refer to the first (and only) field returned
and give that an alias, in this case "id".

In some SQL dialects you can use "select 1" to select the first field,
"select 2" to select the 2nd field and so on.

Any suggestions?

regards,
Jan

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] SELECT 1st field

[Misa Simic]

Both works fine:

SELECT generate_series AS id FROM generate_series(1,5);

and

SELECT id FROM generate_series(1,5) AS foo(id);

Technically dont know is there any differenece...

Thanks,

Misa

2012/5/15 Tom Lane 

> Jan Bakuwel  writes:
> > What I need is the ability to name the column in the view, ie.
>
> > create view v as select 1 as "id" from func(5);
>
> I think what you're looking for is the ability to re-alias a column name,
> for example
>
>select id from func(5) as foo(id);
>
>regards, tom lane
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] returning the number of rows output by a copy command from a function

[Misa Simic]

Hi

Maybe:


1.

strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL

strSQL := 'Select count(*) from (select MyColumns from MyExportTable) t';
Execute strSQL into export_count;

Return export_count;


Kind Regards,

Misa

On Wednesday, January 16, 2013, James Sharrett wrote:

> I have a function that generates a table of records and then a SQL
> statement that does a COPY into a text file.  I want to return the number
> of records output into the text file from my function.  The number of rows
> in the table is not necessarily the number of rows in the file due to
> summarization of data in the table on the way out.  Here is a very
> shortened version of what I'm doing:
>
>
> CREATE OR REPLACE FUNCTION export_data(list of parameters)
>   RETURNS integer AS
> $BODY$
>
> declare
> My variables
>
> Begin
>
>  { A lot of SQL to build and populate the table of records to export}
>
>
> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
> CSV HEADER;';
> Execute strSQL;
>
> Return 0;
>
> end
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>
> strSQL gets dynamically generated so it's not a static statement.
>
> This all works exactly as I want.  But when I try to get the row count
> back out I cannot get it.  I've tried the following:
>
> 1.
> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
> CSV HEADER;';
> Execute strSQL into export_count;
>
> Return export_count;
>
> This give me an error saying that I've tried to use the INTO statement
> with a command that doesn't return data.
>
>
> 2.
> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
> CSV HEADER;';
> Execute strSQL;
>
> Get diagnostics export_count = row_count;
>
> This always returns zero.
>
> 3.
> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
> CSV HEADER;';
> Execute strSQL;
>
> Return row_count;
>
> This returns a null.
>
> Any way to do this?
>
>
> Thanks in advance,
> James
>
>

[SQL] returning the number of rows output by a copy command from a function

[Misa Simic]

I meant the count from the same query as for copy command what actually go
to file... Not count rows from table...

But i agree could be slow...

Cheers,

Misa



On Wednesday, January 16, 2013, James Sharrett wrote:

> The # rows in the table <> # rows in the file because the table is grouped
> and aggregated so simple table row count wouldn't be accurate.  The table
> can run in the 75M - 100M range so I was trying to avoid running all the
> aggregations once to output the file and then run the same code again just
> to get a count.
>
>
>
>
> On 1/16/13 11:36 AM, "Rob Sargent"  wrote:
>
> >On 01/16/2013 09:30 AM, James Sharrett wrote:
> >> I have a function that generates a table of records and then a SQL
> >> statement that does a COPY into a text file.  I want to return the
> >> number of records output into the text file from my function.  The
> >> number of rows in the table is not necessarily the number of rows in the
> >> file due to summarization of data in the table on the way out.  Here is
> >> a very shortened version of what I'm doing:
> >>
> >>
> >> CREATE OR REPLACE FUNCTION export_data(list of parameters)
> >>RETURNS integer AS
> >> $BODY$
> >>
> >> declare
> >> My variables
> >>
> >> Begin
> >>
> >>   { A lot of SQL to build and populate the table of records to export}
> >>
> >>
> >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
> >> CSV HEADER;';
> >> Execute strSQL;
> >>
> >> Return 0;
> >>
> >> end
> >> $BODY$
> >>LANGUAGE plpgsql VOLATILE
> >>
> >> strSQL gets dynamically generated so it's not a static statement.
> >>
> >> This all works exactly as I want.  But when I try to get the row count
> >> back out I cannot get it.  I've tried the following:
> >>
> >> 1.
> >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
> >> CSV HEADER;';
> >> Execute strSQL into export_count;
> >>
> >> Return export_count;
> >>
> >> This give me an error saying that I've tried to use the INTO statement
> >> with a command that doesn't return data.
> >>
> >>
> >> 2.
> >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
> >> CSV HEADER;';
> >> Execute strSQL;
> >>
> >> Get diagnostics export_count = row_count;
> >>
> >> This always returns zero.
> >>
> >> 3.
> >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
> >> CSV HEADER;';
> >> Execute strSQL;
> >>
> >> Return row_count;
> >>
> >> This returns a null.
> >>
> >> Any way to do this?
> >>
> >>
> >> Thanks in advance,
> >> James
> >>
> >declare export_count int;
> >
> >select count(*) from export_table into export_count();
> >raise notice 'Exported % rows', export_count;
> >
> >
> >
> >--
> >Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> >To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] returning the number of rows output by a copy command from a function

[Misa Simic]

The other option would be to create temp table...

Execute dynamic sql to fil temp table

Copy from temp table - copy will return number of rowsx (not inside
execute...)

Drop temp

Kind regards,

Misa

On Wednesday, January 16, 2013, Misa Simic wrote:

> I meant the count from the same query as for copy command what actually go
> to file... Not count rows from table...
>
> But i agree could be slow...
>
> Cheers,
>
> Misa
>
>
>
> On Wednesday, January 16, 2013, James Sharrett wrote:
>
>> The # rows in the table <> # rows in the file because the table is grouped
>> and aggregated so simple table row count wouldn't be accurate.  The table
>> can run in the 75M - 100M range so I was trying to avoid running all the
>> aggregations once to output the file and then run the same code again just
>> to get a count.
>>
>>
>>
>>
>> On 1/16/13 11:36 AM, "Rob Sargent"  wrote:
>>
>> >On 01/16/2013 09:30 AM, James Sharrett wrote:
>> >> I have a function that generates a table of records and then a SQL
>> >> statement that does a COPY into a text file.  I want to return the
>> >> number of records output into the text file from my function.  The
>> >> number of rows in the table is not necessarily the number of rows in
>> the
>> >> file due to summarization of data in the table on the way out.  Here is
>> >> a very shortened version of what I'm doing:
>> >>
>> >>
>> >> CREATE OR REPLACE FUNCTION export_data(list of parameters)
>> >>RETURNS integer AS
>> >> $BODY$
>> >>
>> >> declare
>> >> My variables
>> >>
>> >> Begin
>> >>
>> >>   { A lot of SQL to build and populate the table of records to export}
>> >>
>> >>
>> >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv
>> with
>> >> CSV HEADER;';
>> >> Execute strSQL;
>> >>
>> >> Return 0;
>> >>
>> >> end
>> >> $BODY$
>> >>LANGUAGE plpgsql VOLATILE
>> >>
>> >> strSQL gets dynamically generated so it's not a static statement.
>> >>
>> >> This all works exactly as I want.  But when I try to get the row count
>> >> back out I cannot get it.  I've tried the following:
>> >>
>> >> 1.
>> >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv
>> with
>> >> CSV HEADER;';
>> >> Execute strSQL into export_count;
>> >>
>> >> Return export_count;
>> >>
>> >> This give me an error saying that I've tried to use the INTO statement
>> >> with a command that doesn't return data.
>> >>
>> >>
>> >> 2.
>> >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv
>> with
>> >> CSV HEADER;';
>> >> Execute strSQL;
>> >>
>> >> Get diagnostics export_count = row_count;
>> >>
>> >> This always returns zero.
>> >>
>> >> 3.
>> >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv
>> with
>> >> CSV HEADER;';
>> >> Execute strSQL;
>> >>
>> >> Return row_count;
>> >>
>> >> This returns a null.
>> >>
>> >> Any way to do this?
>> >>
>> >>
>> >> Thanks in advance,
>> >> James
>> >>
>> >declare export_count int;
>> >
>> >select count(*) from export_table into export_count();
>> >raise notice 'Exported % rows', export_count;
>> >
>> >
>> >
>> >--
>> >Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> >To make changes to your subscription:
>> >http://www.postgresql.org/mailpref/pgsql-sql
>>
>>
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>

Re: [SQL] Summing & Grouping in a Hierarchical Structure

[Misa Simic]

Hi,


Have you considered maybe ltree datatype?

http://www.postgresql.org/docs/9.1/static/ltree.html

I think it solves a lot of problems in topic

Kind regards,

Misa

On Friday, February 15, 2013, Don Parris wrote:

> Hi all,
>
> I posted to this list some time ago about working with a hierarchical
> category structure.   I had great difficulty with my problem and gave up
> for a time.  I recently returned to it and resolved a big part of it.  I
> have one step left to go, but at least I have solved this part.
>
> Here is the original thread (or one of them):
>
> http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TLLSW=rmo1v...@mail.gmail.com
>
> Here is my recent blog post about how I managed to show my expenses summed
> and grouped by a mid-level category:
> http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/
>
>
> Specifically, I wanted to sum and group expenses according to categories,
> not just at the bottom tier, but at higher tiers, so as to show more
> summarized information.  A CEO primarily wants to know the sum total for
> all the business units, yet have the ability to drill down to more detailed
> levels if something is unusually high or low.  In my case, I could see the
> details, but not the summary.  Well now I can summarize by what I refer to
> as the 2nd-level categories.
>
> Anyway, I hope this helps someone, as I have come to appreciate - and I
> mean really appreciate - the challenge of working with hierarchical
> structures in a 2-dimensional RDBMS.  If anyone sees something I should
> explain better or in more depth, please let me know.
>
> Regards,
> Don
> --
> D.C. Parris, FMP, Linux+, ESL Certificate
> Minister, Security/FM Coordinator, Free Software Advocate
> http://dcparris.net/ 
> 
> GPG Key ID: F5E179BE
>

Re: [SQL] Summing & Grouping in a Hierarchical Structure

[Misa Simic]

Hi Don,

Yes, its better to use it in category table...

Bryan, how many levels there will be - we dont know...

With one table - and ltree is solved all cases...

To add new subcategory user just picks the parent category... So it is easy
to add chain ring to gear... As category...

In another transaction table is category_id, amount...


Don already posted query for sum... In these case just category and
transaction table should be joined  sum amount, group by functions on
lpath(depending what is the goal...)

Kind Regards,

Misa



On Saturday, February 23, 2013, Bryan L Nuse wrote:

>
>>
>> This works fine:
>> test_ltree=> SELECT path, trans_amt FROM testcat;
>>   path   | trans_amt
>> -+---
>>  TOP.Transportation.Auto.Fuel| 50.00
>>  TOP.Transportation.Auto.Maintenance | 30.00
>>  TOP.Transportation.Auto.Fuel| 25.00
>>  TOP.Transportation.Bicycle.Gear | 40.00
>>  TOP.Transportation.Bicycle.Gear | 20.00
>>  TOP.Transportation.Fares.Bus| 10.00
>>  TOP.Transportation.Fares.Train  |  5.00
>>  TOP.Groceries.Food.Beverages| 30.00
>>  TOP.Groceries.Food.Fruit_Veggies| 40.00
>>  TOP.Groceries.Food.Meat_Fish| 80.00
>>  TOP.Groceries.Food.Grains_Cereals   | 30.00
>>  TOP.Groceries.Beverages.Alcohol.Beer| 25.00
>>  TOP.Groceries.Beverages.Alcohol.Spirits | 10.00
>>  TOP.Groceries.Beverages.Alcohol.Wine| 50.00
>>  TOP.Groceries.Beverages.Juice   | 45.00
>>  TOP.Groceries.Beverages.Other   | 15.00
>> (16 rows)
>>
>>
>>  So if I want to see:
>>  TOP.Groceries| 240.00
>>  TOP.Transportation | 180.00
>>
>>
>>
>  Hello Don,
>
>  Perhaps I am missing something about what your constraints are, or what
> you're trying to achieve, but is there any reason you could not use a
> series of joined tables indicating parent-child relationships?  The
> following example follows that in your previous posts.  Note that this
> approach (as given) will not work if branches stemming from the same node
> are different lengths.  That is, if you have costs associated with
> "Transportation.Bicycle.Gear", you could not also have a category
> "Transportation.Bicycle.Gear.Chain_ring".  (To add the latter category,
> you'd have to put costs from the former under something like
> "Transportation.Bicycle.Gear.General" -- or modify the approach.)  However,
> lengthening the "Alcohol" branches, e.g., by tacking on a level5 table
> would be easy.  Notice that level3 and level4 are not true look-up
> tables, since they may contain duplicate cat values.
>
>  If I'm off base, by all means specify just how.
>
>  Regards,
> Bryan
>
>  --
>
>  CREATE TABLE level1 (
>   cat   text  PRIMARY KEY
> );
>
>  CREATE TABLE level2 (
>cat   text   PRIMARY KEY,
>parent   text   REFERENCES level1(cat)
> );
>
>  CREATE TABLE level3 (
>cat   text,
>parent   text   REFERENCES level2(cat),
>cost   numeric(6,2)
> );
>
>  CREATE TABLE level4 (
>cat   text,
>parent   text,
>cost   numeric(6,2)
> );
>
>
>  INSERT INTO level1
>   VALUES ('Transportation'),
>  ('Groceries');
>
>  INSERT INTO level2
>   VALUES ('Auto', 'Transportation'),
>  ('Bicycle', 'Transportation'),
>  ('Fares', 'Transportation'),
>  ('Food', 'Groceries'),
>  ('Beverages', 'Groceries');
>
>  INSERT INTO level3
>   VALUES ('Fuel', 'Auto', 50.00),
>  ('Maintenance', 'Auto', 30.00),
>  ('Fuel', 'Auto', 25.00),
>  ('Gear', 'Bicycle', 40.00),
>  ('Gear', 'Bicycle', 20.00),
>  ('Bus', 'Fares', 10.00),
>  ('Train', 'Fares', 5.00),
>  ('Beverages', 'Food', 30.00),
>  ('Fruit_Veg', 'Food', 40.00),
>  ('Meat_Fish', 'Food', 80.00),
>  ('Grains_Cereals', 'Food', 30.00),
>  ('Alcohol', 'Beverages', NULL),
>  ('Juice', 'Beverages', 45.00),
>  ('Other', 'Beverages', 15.00);
>
>  INSERT INTO level4
>   VALUES ('Beer', 'Alcohol', 25.00),
>  ('Spirits', 'Alcohol', 10.00),
>  ('Wine', 'Alcohol', 50.00);
>
>
>  CREATE VIEW all_cats AS (
> SELECT a.cat AS level4,
>b.cat AS level3,
>c.cat AS level2,
>d.cat AS level1,
>CASE WHEN a.cost IS NULL THEN 0
> WHEN a.cost IS NOT NULL THEN a.cost
>  END
>+ CASE WHEN b.cost IS NULL THEN 0
>   WHEN b.cost IS NOT NULL THEN b.cost
>  END AS cost
>   FROM level4 a
> FULL JOIN
> level3 b
> ON (a.parent = b.cat)
>   FULL JOIN
>   level2 c
>   ON (b.parent = c.cat)
> FULL JOIN
> level1 d
> ON (c.parent = d.cat)
>   ORDER BY level1, level2, level3, level4
> );
>
>
>
>  SELECT * FROM all_cats;
>
>   level4  | level3 |  level2   | level

Re: [SQL] Summing & Grouping in a Hierarchical Structure

[Misa Simic]

Hi Don,

To be honest with you - i dont know - but think it is not...

We use it to solve the problem with hierarchy relations - but it is nowhere
"visible" to users in the app...

Our internal rule is to use ids in ltree structure to solve many others
problems, actually to easy get, actual category info... From any point...

So if needed, it is easy from 1.2.3.4, get: TOP.Groceries.Food.Herbs &
Spices if needed... Each of them are actually category names in the table...

Kind regards,

Misa


On Saturday, February 23, 2013, Don Parris wrote:

> Misa,
>
> Is it possible to use spaces in the ltree path, like so:
> TOP.Groceries.Food.Herbs & Spices
>
> Or do the elements of the path have to use underscores and dashes?
>
>
> On Sat, Feb 23, 2013 at 7:19 AM, Misa Simic  wrote:
>
> Hi Don,
>
> Yes, its better to use it in category table...
>
> Bryan, how many levels there will be - we dont know...
>
> With one table - and ltree is solved all cases...
>
> To add new subcategory user just picks the parent category... So it is
> easy to add chain ring to gear... As category...
>
> In another transaction table is category_id, amount...
>
>
> Don already posted query for sum... In these case just category and
> transaction table should be joined  sum amount, group by functions on
> lpath(depending what is the goal...)
>
> Kind Regards,
>
> Misa
>
>
>
> On Saturday, February 23, 2013, Bryan L Nuse wrote:
>
>
>
> This works fine:
> test_ltree=> SELECT path, trans_amt FROM testcat;
>   path   | trans_amt
> -+---
>  TOP.Transportation.Auto.Fuel| 50.00
>  TOP.Transportation.Auto.Maintenance | 30.00
>  TOP.Transportation.Auto.Fuel| 25.00
>  TOP.Transportation.Bicycle.Gear | 40.00
>  TOP.Transportation.Bicycle.Gear | 20.00
>  TOP.Transportation.Fares.Bus| 10.00
>  TOP.Transportation.Fares.Train  |  5.00
>  TOP.Groceries.Food.Beverages| 30.00
>  TOP.Groceries.Food.Fruit_Veggies| 40.00
>  TOP.Groceries.Food.Meat_Fish| 80.00
>  TOP.Groceries.Food.Grains_Cereals   | 30.00
>  TOP.Groceries.Beverages.Alcohol.Beer| 25.00
>  TOP.Groceries.Beverages.Alcohol.Spirits | 10.00
>  TOP.Groceries.Beverages.Alcohol.Wine| 50.00
>  TOP.Groceries.Beverages.Juice   | 45.00
>  TOP.Groceries.Beverages.Other   | 15.00
> (16 rows)
>
>
>  So if I want to see:
>  TOP.Groceries| 240.00
>  TOP.Transportation | 180.00
>
>
>
>  Hello Don,
>
>  Perhaps I am missing something about what your constraints are, or what
> you're trying to achieve, but is there any reason you could not use a
> series of joined tables indicating parent-child relationships?  The
> following example follows that in your previous posts.  Note that this
> approach (as given) will not work if branches stemming from the same node
> are different lengths.  That is, if you have costs associated with
> "Transportation.Bicycle.Gear", you could not also have a category
> "Transportation.Bicycle.Gear.Chain_ring".  (To add the latter category,
> you'd have to put costs from the former under something like
> "Transportation.Bicycle.Gear.General" -- or modify the approach.)  However,
> lengthening the "Alcohol" branches, e.g., by tacking on a level5 table
> would be easy.  Notice that level3 and level4 are not true look-up
> tables, since they may contain duplicate cat values.
>
>  If I'm off base, by all means specify just how.
>
>  Regards,
> Bryan
>
>  --
>
>  CREATE TABLE level1 (
>   cat   text  PRIMARY KEY
> );
>
>  CREATE TABLE level2 (
>cat   text   PRIMARY KEY,
>parent   text   REFERENCES level1(cat)
> );
>
>  --
> D.C. Parris, FMP, Linux+, ESL Certificate
> Minister, Security/FM Coordinator, Free Software Advocate
> http://dcparris.net/ 
> <https://www.xing.com/profile/Don_Parris><http://www.linkedin.com/in/dcparris>
> GPG Key ID: F5E179BE
>

Re: [SQL] Using Ltree For Hierarchical Structures

[Misa Simic]

Hi Igor,

I agree it is all "in the eyes of beholder".

Would be good if you can show how to achieve the goal (Summing on Top
Levels categories in hierarchy) with CTE?

For example show all categories in level 2 (x), and sum amounts for each...
(Sum takes all amounts from all transactions of its child categories in any
bellow levels).

I have tested both scenarios - and indexed ltree has given better result -
though there is a possibility I haven't pick best approach to solve the
problem with CTE...

I am just interested in performance - implementation detail is less
important...

Data:

Total number of categories: 1000 (in all levels)
No of Categories in top level: 5
No of categories in level 2: 20
Total number of levels: can vary - max in my testing was 8...


Transaction rows with amounts: 1 000 000


(though I am not sure what u meant by: "2-table design using ltree", and
with CTE there are 2 tables... Categories and Transactions: just in
categories instead of ltree datatype, is integer datatype: parent_id)

Many thanks,

Misa


2013/2/26 Igor Neyman 

>
>
> From: Don Parris [mailto:parri...@gmail.com]
> Sent: Sunday, February 24, 2013 5:21 PM
> To: pgsql-sql@postgresql.org
> Subject: Using Ltree For Hierarchical Structures
>
> Hi all,
> With many thanks to Misa and others who helped out with my question about
> working with hierarchical data, I have now written a blog post on how I
> implemented the ltree module to solve my problem.
>
> http://dcparris.net/2013/02/24/using-ltree-hierarchical-postgresql/
> Frankly, if you work with hierarchical data, I'm not sure I could
> recommend it strongly enough.  I should think that even experienced,
> advanced SQL gurus would appreciate the simplicity ltree offers, when
> compared to the ugly table designs and recursive queries in order to work
> with hierarchical structures.
> I really hope this blog post will help others in the same boat.
>
>
> Regards,
> Don
>
>
> It's all "in the eyes of beholder".
> IMHO, recursive CTEs are perfect for hierarchical structures, and much
> cleaner than 2-table design using ltree, that you show in the blog.
>
> Regards,
> Igor Neyman
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: [SQL] Using Ltree For Hierarchical Structures

[Misa Simic]

2013/2/26 Thomas Kellerer 

> Don Parris wrote on 24.02.2013 23:20:
>
>  With many thanks to Misa and others who helped out with my question
>> about working with hierarchical data, I have now written a blog post
>> on how I implemented the ltree module to solve my problem.
>>
>> http://dcparris.net/2013/02/**24/using-ltree-hierarchical-**postgresql/
>>
>> Frankly, if you work with hierarchical data, I'm not sure I could
>> recommend it strongly enough.  I should think that even experienced,
>> advanced SQL gurus would appreciate the simplicity ltree offers, when
>> compared to the ugly table designs and recursive queries in order to
>> work with hierarchical structures.
>>
>> I really hope this blog post will help others in the same boat.
>>
>>
> How do you ensure referential integrity with this approach?
> (i.e. make sure that all elements from the path column actually point to
> an existing category)
>
> Thomas


Hi Thomas,

Yes we met that problem and it further makes deeper problems... i.e. what
if  some category in up level - change his parent (updated path field) -
path must be changed for all childs...

Of several solutions - we have picked to use the best from both worlds...

So we still use - parent_id column... and ltree is used just as
materialized path - to improve performance... I think Materialized Views
what comming in 9.3 - (I still havent seen how it works) -  will help in
that way - we will see...

Re: [SQL] Efficiency Problem

[Misa Simic]

Hi,

1) Is function marked as immutable?

2) if immutable doesnt help... It should be possible execute it first, and
use it in other dynamics things in where...

Cheers,

Misa

Sent from my Windows Phone
--
From: Surfing
Sent: 17/03/2013 12:16
To: pgsql-sql@postgresql.org
Subject: [SQL] Efficiency Problem

  Hi all,
I'm composing a query from a web application of type:

*SELECT * FROM table WHERE a_text_field LIKE replace_something ('%**
a_given_string**%');*

The function replace_something( ... ) is a stored procedure that replaces
some particular characters with others.
The problem is that I noticed that this query is inefficient... and I think
that the replace_something ( ... ) function is called for each row of the
table.

This observation is motivated by the fact that it takes around 30 seconds
to execute on the table (of about 25,000 rows), whereas if I execute:
*SELECT * FROM table WHERE a_text_field LIKE '**pre_processed_string
';*

where* pre_processed_string** *is the result of the application of
replace_something ('%*a_given_string*%')  it just takes 164ms.

The execution of
*SELECT replace_something ('%**a_given_string**%')*
 takes only 14ms.

Summarizing,
- Replace function: 14ms
- SELECT query without replace function: 164ms
- SELECT query with replace function:  30.000ms

Morever, I cannot create a stored procedure that precalculate the
*pre_processed_string
*and executes the query, since I dinamically
compose other conditions in the WHERE clause.

Any suggestion?

Thank you.
**

Re: [SQL] Correct implementation of 1:n relationship with n>0?

[Misa Simic]

2013/4/30 Wolfgang Keller 

> It hit me today that a 1:n relationship can't be implemented just by a
> single foreign key constraint if n>0. I must have been sleeping very
> deeply not to notice this.
>
> E.g. if there is a table "list" and another table "list_item" and the
> relationship can be described as "every list has at least one
> list_item" (and every list_item can only be part of one list, but
> this is trivial).
>
> A "correct" solution would require (at least?):
>
> 1. A foreign key pointing from each list_item to its list
>
> 2. Another foreign key pointing from each list to one of its list_item.
> But this must be a list_item that itself points to the same list, so
> just a simple foreign key constraint doesn't do it.
>
> 3. When a list has more than one list_item, and you want to delete the
> list_item that its list points to, you have to "re-point" the foreign
> key constraint on the list first. Do I need to use stored proceures
> then for all insert, update, delete actions?
>
> (4. Anything else that I've not seen?)
>
> Is there a "straight" (and tested) solution for this in PostgreSQL, that
> someone has already implemented and that can be re-used?
>
> No, I definitely don't want to get into programming PL/PgSQL myself.
> especially if the solution has to warrant data integrity under all
> circumstances. Such as concurrent update, insert, delete etc.
>
> TIA,
>
> Sincerely,
>
> Wolfgang
>


I don't think there is the way to achieve that without programming (less
important in which language...)

Your rules say:

1) End user - can't be able to create new list at all... (just new List)
(If he can create new list - it will brake the your rule 2)

He always creates "list_item" - but in one case - should pick existing
"list" in another he must enter info about new list_item together with info
about new list

Technically - create new list_item calls one or another function

2) End User - just can delete list_item (function will make additional
check - if there is no more list_items in my list - delete the list as well
- the same check will be run after "repoint")

Everything else - will be assured with existing FK integrity

Re: [SQL] Correct implementation of 1:n relationship with n>0?

[Misa Simic]

2013/4/30 Anton Gavazuk 

> Hi,
>
> Can you explain what you are trying to achieve because it's not clear...
>
> There are 2 types of relationships which might be used in your case:
>
> 1) unidirectional relationship from list_item to list through foreign
> key on list
> 2) bidirectional relationship implemented through join table which
> contains references between both tables
> These are pretty standard  generic techniques applied many times and
> don't require any "programming"
>
> Thanks,
> Anton
>
> On Apr 30, 2013, at 16:39, Wolfgang Keller  wrote:
>
> > It hit me today that a 1:n relationship can't be implemented just by a
> > single foreign key constraint if n>0. I must have been sleeping very
> > deeply not to notice this.
> >
> > E.g. if there is a table "list" and another table "list_item" and the
> > relationship can be described as "every list has at least one
> > list_item" (and every list_item can only be part of one list, but
> > this is trivial).
> >
> > A "correct" solution would require (at least?):
> >
> > 1. A foreign key pointing from each list_item to its list
> >
> > 2. Another foreign key pointing from each list to one of its list_item.
> > But this must be a list_item that itself points to the same list, so
> > just a simple foreign key constraint doesn't do it.
> >
> > 3. When a list has more than one list_item, and you want to delete the
> > list_item that its list points to, you have to "re-point" the foreign
> > key constraint on the list first. Do I need to use stored proceures
> > then for all insert, update, delete actions?
> >
> > (4. Anything else that I've not seen?)
> >
> > Is there a "straight" (and tested) solution for this in PostgreSQL, that
> > someone has already implemented and that can be re-used?
> >
> > No, I definitely don't want to get into programming PL/PgSQL myself.
> > especially if the solution has to warrant data integrity under all
> > circumstances. Such as concurrent update, insert, delete etc.
> >
> > TIA,
> >
> > Sincerely,
> >
> > Wolfgang
> >
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
>
>
"
2) bidirectional relationship implemented through join table which
contains references between both tables
"

What is an example of that?