[SQL] Using functions in SQL statements
I would dearly love to do CREATE GROUP foo WITH USER CURRENT_USER; in a script to psql(1), but this does not appear to be supported by the parser. Two questions: 1. Does anybody have a good work-around for this? 2. Is there a document somewhere that says where functions are allowed in SQL statements? --- Allan. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Creating foreign key constraint to child table?
I would like to create a FOREIGN KEY constraint to an inherited column, like: test=# CREATE TABLE foo(id INTEGER PRIMARY KEY); test=# CREATE TABLE bar() INHERITS (foo); test=# CREATE TABLE baz (bar INTEGER, CONSTRAINT fk_bar FOREIGN KEY (bar) REFERENCES bar(id)); ERROR: UNIQUE constraint matching given keys for referenced table "bar" not found This obvioulsy doesn't work. I *can* create a FOREIGN KEY contraint to the parent table: test=# create table baz(bar integer, constraint fk_bar foreign key (bar) references foo(id)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE but this is not exactly what I want: I need to ensure that baz.bar is a bar and not just any foo. Do I need to write my own INSERT/UPDATE triggers on baz to check the tableoid, or is there a nice way to do this? Any examples on how to do this? In particular, do I need to do a SELECT on pg_class for every INSERT / UPDATE in baz, just to get the tableoid for bar ? There *is* an index on pg_class.relname but still... --- Allan. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Creating foreign key constraint to child table?
I obviously haven't had enough coffee yet... :-) The following script works as expected. drop database test; create database test; \c test create table foo (id integer primary key); create table bar () inherits (foo); create unique index bar_id_idx ON bar(id); create table baz (bar integer, constraint fk_bar foreign key (bar) references bar(id)); insert into foo values (1); insert into bar values (2); insert into baz values (2); insert into baz values (1); -- fails Sorry. --- Allan. I wrote: > I would like to create a FOREIGN KEY constraint to an inherited column, like: > > test=# CREATE TABLE foo(id INTEGER PRIMARY KEY); > test=# CREATE TABLE bar() INHERITS (foo); > test=# CREATE TABLE baz (bar INTEGER, CONSTRAINT fk_bar FOREIGN KEY (bar) >REFERENCES bar(id)); > ERROR: UNIQUE constraint matching given keys for referenced table "bar" not >found > > This obvioulsy doesn't work. I *can* create a FOREIGN KEY contraint to the parent >table: > > test=# create table baz(bar integer, constraint fk_bar foreign key (bar) >references foo(id)); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > CREATE > > but this is not exactly what I want: I need to ensure that baz.bar is a bar and not >just any foo. > > Do I need to write my own INSERT/UPDATE triggers on baz to check the tableoid, or is >there a nice way to do this? > > Any examples on how to do this? In particular, do I need to do a SELECT on pg_class >for every INSERT / UPDATE in baz, just to get the tableoid for bar ? There *is* an >index on pg_class.relname but still... > > --- Allan. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Creating foreign key constraint to child table?
Stephan Szabo wrote: > On Sun, 5 Aug 2001, Allan Engelhardt wrote: [see new example below] > Not that this is related to what you asked about precisely (I saw the > response you made), but the query above also doesn't do what you think > it does right now. It currently makes a reference to only foo, not > any subchildren of foo. Oh, man! You are right, but this sux big time: there should not be an asymmetry between a FOREIGN KEY constraint and the SELECT statement. Now that the default is SQL_INHERITANCE ON, it should be the default for the constraint as well, IMHO. 1. Am I the only one who thinks this is a bug? 2. How would I get the behaviour I expect? Write my own trigger? :-P --- Allan. test=# create table foo (id integer primary key); test=# create table bar () inherits (foo); test=# create table baz (bar integer, constraint fk_bar foreign key (bar) references foo(id)); test=# insert into foo values (1); test=# insert into bar values (2); test=# insert into baz values (2); ERROR: fk_bar referential integrity violation - key referenced from baz not found in foo test=# select * from foo where id = 2; id 2 (1 row) test=# ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Inheritance is completely broken [was: Re: Creating foreign key constraint to child table?]
Dimitri pointed out (the post does not seem to have appered yet) that you can also do: test=# create table foo(id integer primary key); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE test=# create table bar () inherits (foo); CREATE test=# insert into foo values (1); INSERT 12734236 1 test=# insert into foo values (1); ERROR: Cannot insert a duplicate key into unique index foo_pkey test=# insert into bar values (1); INSERT 12734238 1 test=# select * from foo; id 1 1 (2 rows) So inheritance does seem to be completely broken. There is also an entry in the TODO list Allow inherited tables to inherit index, UNIQUE constraint, and primary key, foreign key [inheritance] which seems to be related. It doesn't have a dash, so I guess I won't hold my breath.... Now I'm sad. Allan. Allan Engelhardt wrote: > Stephan Szabo wrote: > > > On Sun, 5 Aug 2001, Allan Engelhardt wrote: > > [see new example below] > > > Not that this is related to what you asked about precisely (I saw the > > response you made), but the query above also doesn't do what you think > > it does right now. It currently makes a reference to only foo, not > > any subchildren of foo. > > Oh, man! You are right, but this sux big time: there should not be an asymmetry >between a FOREIGN KEY constraint and the SELECT statement. Now that the default is >SQL_INHERITANCE ON, it should be the default for the constraint as well, IMHO. > > 1. Am I the only one who thinks this is a bug? > > 2. How would I get the behaviour I expect? Write my own trigger? :-P > > --- Allan. > > test=# create table foo (id integer primary key); > test=# create table bar () inherits (foo); > test=# create table baz (bar integer, >constraint fk_bar foreign key (bar) references foo(id)); > test=# insert into foo values (1); > test=# insert into bar values (2); > test=# insert into baz values (2); > ERROR: fk_bar referential integrity violation - key referenced from baz not found >in foo > test=# select * from foo where id = 2; > id > > 2 > (1 row) > > test=# ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: Data type confusion
Josh Berkus wrote: > This is a multi-part MIME message > > --_===97089davinci.ethosmedia.com===_ > Content-Type: text/plain; charset="ISO-8859-1" > Content-Transfer-Encoding: 8bit > > Tom, Stephan, > > I'm writing up the date/time FAQ, and I came across some operator > behavior that confuses me: > > If > INTERVAL / INTEGER = INTERVAL > > then why does > INTERVAL / INTERVAL = ERROR? > > Shouldn't > INTERVAL / INTERVAL = INTEGER? > > I'd like to answer this before I finish the FAQ, as it seems > inconsistent behavior. > > -Josh > > Josh, I'm not Tom or Stephan (sorry) but in your scenario what would be the result of, say, dividing '3 months ago' with '6 seconds' in the future? I don't think it makes conceptual sense to divide intervals Allan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: Data type confusion
Josh Berkus wrote:
> Or, to put it another way, 95% of the time users just want to do simple
> things. Like we want to know how many weeks an employee has been with
> us for: '2 years 3 months'::INTERVAL / '1 week'::INTERVAL (and we
> don't care about the fractional week left over).
> Thus we don't want to hold up simple and obvious date multiplication and
> division just to deal with the wierdo cases.
I see now what you are trying to do. It sort of makes sense, but I'm still really
reluctant to give (semantic or otherwise) meaning to "yesterday divided by tomorrow"
.
Would it be a better solution if there was a conversion function a la
convert(text, interval) RETURNS double precision
where text in ('day','week','hour', ). The function would convert the interval to
the specified unit, with some considerable degree of fuzziness as discussed in this
thread.
Then your query would be simply
convert('week', '2 years 3 months') / 1
and would return something in the vicinity of 117.0 :-)
It seems to me that such a function would be more generally useful than the division
of intervals. What you really want to do is not to divide intervals, but to express
them in different time units. Or am I missing something (again)?
Comments?
Allan.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Re: prob with PERL/Postgres
1. I'm assuming that psql works fine? psql -h 127.0.0.1 mpact? 1b. Have you checked the port?? You ARE running postmaster with the '-i' option, aren't you? The recommended solutions are typically to use the DBI and DBD modules. Try man DBD::Pg. Alternatively: have you tried the setdbLogin method instead: $conn = Pg::setdbLogin($pghost, $pgport, $pgoptions, $pgtty, $dbname, $login, $pwd) Opens a new connection to the backend. The connection identifier $conn ( a pointer to the PGconn structure ) must be used in subsequent commands for unique identifica- tion. Before using $conn you should call $conn->status to ensure, that the connection was properly made. Closing a connection is done by deleting the connection handle, eg 'undef $conn;'. Hope this helps a little.. Allan Kristopher Yates wrote: > PERL SNIPPET: > > # build arrays from file (OMITTED) > > use Pg; > $dbhost='127.0.0.1'; > $dbname='mpact'; > #$connstr="dbname=$dbname"; > $connstr="host=$dbhost dbname=$dbname"; > $conn = Pg::connectdb($connstr); > > #more code related to date omitted > > $result=$conn->exec($sql); > (PGRES_COMMAND_OK eq $result->resultStatus) > or die $conn->errorMessage; > > WHY DO I GET PQsendQuery() -- There is no connection to the > backend. I have tried leaving host blank, using IP 127.0.0.1 and > hostname localhost. This script should work - the problem is > something with postgres but I dont know what. Any ideas out > there? Thanks, Kris > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] [warning: largely off-topic] Re: Data type confusion
Josh, Thanks for your explanation. I'd like to get hold of a copy of SQL99/PKG001 to see what they have actually defined. I think the INTERVAL type sux :-) Long rant follows - consider hitting the delete button now. In this area, there are a number of different concepts that it would make sense to separate. Let's call one UTIME. It is time defined as (the time-coordinate of) an event in the history of the Universe. [We'll ignore Einstein for the following.] It has physical meaning. Let call another LDATE. It is what you and I normally call a date. Specifically, it is a legal (or social or religious) representation of a UTIME. You can make a contract (legal with man, or religiously with God) using LDATEs. Let's invent a CALENDAR. It translates between UTIME and LDATE. It changes all the time ;-( Well, maybe not *all* the time but it is sufficient unstable to be a problem for some applications. There is not only the problem of Julian vs Gregorian vs some other calendar. Consider the issue of changing the rules governing summer-time. It is not long ago that Britain changed the rule for ending summer time from being the fourth Sunday in October to being the last Sunday in October. Some countries seem to decide on a year to year basis if they want to have summer time. Finally, the international committee that governs the CALENDAR only have to give eight weeks notice when it introduces a leap-second. (Leap seconds are the reason that struct tm.tm_sec has a range of 0,...,61.) This means that the CALENDAR is, strictly speaking, only known eight weeks in advance. It also highlights the difference between LDATE and UTIME: if we have a contract for me to start a machine on a specific LDATE and a leap second is introduced between now and then, then I'd better change that sleep(n) statement in my control program to sleep(n+1) or the factory will start too early. I once spent an unhappy week debugging a problem related to this :-( It really would have started the factory one hour too late. Now of course you can define deltas. Unix systems kind of keep UTIME using a delta: a variable of type time_t holds the number of seconds since a specific event (defined as a given LDATE for a fixed CALENDAR). Let's call them DUTIME and DLDATE. They are conceptually different: One day of DLDATE may be 23, 24, or 25 hours of DUTIME, depending on summer time rules. It may be 86,400 or 86,401 (rarely: 86,402) seconds depending on leap seconds. Important: There is no meaningful translation between DUTIME and DLDATE except for a fixed (start- or end-) UTIME (or DLDATE). (No, really!!) Let's introduce a final concept: a unit of UTIME. Maybe we call it TIMEU. You need to measure UTIME is something, maybe seconds is the fundamental unit (as in SI) and you have other, derived units. We don't need a similar concept for LDATE - it is effectively provided by the CALENDAR. It is it that which defines years, months, or whatever the Incas used to use... Now BACK ON-TOPIC: What's an INTERVAL supposed to be? DUTIME, DLDATE, TIMEU or something that really belongs to the CALENDAR? All of the above? Methinks SQL99 (or PostgreSQL's implementation) is going for the latter option. Methinks it is confusing. But then, I'm thick. :-) Apologies for the interruption -- We now continue the regular scheduled program Allan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] how can i return multiple values from a function
Try using temporary tables. Functions can't return tables and, it would seem, SETOFs.
srinivas wrote:
> i have tried retrieving multiple values using setof function but i
> couldnt solve it.when i am trying using setof iam getting this as
> output.
>
>
> 1 CREATE FUNCTION hobbies (varchar) RETURNS SETOF bank
> 2 AS 'SELECT * FROM bank
> 3 '
> 4 LANGUAGE 'sql';
>
> ~
> output:
> select hobbies('srinivas') as col;
> col
> ---
> 137462096
> 137462096
> (2 rows)
>
> please let me know what is the alternative and if possible with a sample
> piece of code.
>
> cheers
> chowdary.
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Function return rows?
Pat M wrote: > Can I use a function to execute a query and return a row or set of rows? No. Consider using temporary tables if you must do this. > If > so, can you point me to some examples or perhaps give me an example of a > function that would do roughly the same thing as: > > select * from mytable where mytable.name ~* 'aname'; > > I can't seem to find any examples that return rows, just single ints and > bools and stuff. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ROUND function ??
Tom Lane wrote:
> [...]Because the IEEE float math standard says so. Round-to-nearest-even
> is considered good practice.
I learn something new every day. :-)
While it is true that IEEE 754 defaults to "round-to-nearest", which means rounding
midway points to even, it is slightly confusing that this is implemented in the math
library (libc) by the rint function (subject to compiler defaults and fesetround
calls) while the round functions ((ll|l)?round(f|l)?) of the library does indeed
'round half-way cases away from zero to the nearest integer', which is also the
mathematical behavior. Try
% info libc Arithmetic Rounding
% info libc Arithmetic 'Arithmetic Functions' 'Rounding Functions'
on you local friendly U*ix clone and you shall find enlightenment...
The SQL standard seems to leave it implementation dependent ('92, sec 4.4.1):
An approximation obtained by rounding of a numerical value N for
an T is a value V representable in T such
that the absolute value of the difference between N and the nu-
merical value of V is not greater than half the absolute value
of the difference between two successive numerical values repre-
sentable in T. If there are more than one such values V, then it is
implementation-defined which one is taken.
So PostgreSQL does "the right thing" (again!).
Allan.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] indexing and LIKE
Patrik Kudo wrote: > [...] > > Is it at all possible to create an index on lower(name), and in that case, > what type of index and using what syntax? You'll want to look at section 7.5 "Functional Indices" in the 7.1.3 'User's Guide'. Allan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] MEDIAN as custom aggregate?
Can't you do something like select age from ages order by age limit 1 offset (select count(*) from ages)/2; except you can't nest the select so you'll have to use a variable to hold it... Make sure it does the right thing when there is an odd number of rows. I don't understand why you want the median and not some parameters of your assumed distribution (mean and variance, for example) but each to his own... Allan. Josh Berkus wrote: > Folks, > > Hey, anybody have a custom aggregate for median calucation? I'm doing > this through a PL/pgSQL function, and a custom aggregate would probably > be faster. > > For those whose stats terminology is rusty, the "median" is the "middle" > value in a distribution. For example, if we had the following data: > > Table ages > person age > Jim 21 > Rusty 24 > Carol 37 > Bob 62 > Leah78 > > Our Median would be Carol's age, 37. This is a different figure from > the Mean, or Average, which is 44.4. Using the combination of the Mean > and the Median you can do all kinds of interesting statistical analysis. > > -Josh Berkus > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
