Re: [SQL] challenging query
> > Consider the following table: > > A B C D select? > --- > 1 FOO A1 100 n > 1 BAR Z2 100 n > 2 FOO A1 101 y > 2 BAR Z2 101 y > 3 FOO A1 102 y > 4 BAR Z2 99 y > 5 FOO A1 99 n > 6 BAR Z2 98 n > 7 FOO AB 103 y > 7 BAR ZY 103 y > > This table has the idea of "groups", that is, a group is defined as > all of the words from B that have the same number A. The values in > column C also matter- we want to select both groups A=7 and A=1 since > they contain different values C. Note that the groups defined by A=1 > and A=3 are distinct- they do not contain the same number of words > from B, so we want to select them both. Also note that D is datetime, > and all the rows with the same number A will have the same D (this is > actually ensured by a single row in another table.) > > I want to select all of the numbers A which define distinct groups and > have the highest datetime D. Is this possible in a SQL query? Sorry, there is an inconsistency in your question, at least to me. In the table's last column you say you want to select A in (2,3,4,7) but in the epilogue you say you want to select A in (7,1,1,3) What did I miss? Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Why the weak key is created as unique
mydb=> create table AAA (a serial primary key); NOTICE: CREATE TABLE will create implicit sequence 'aaa_a_seq' for SERIAL column 'aaa.a' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'aaa_pkey' for table 'aaa' CREATE mydb=> create table BBB (a serial references AAA, b integer, primary key(a,b)); NOTICE: CREATE TABLE will create implicit sequence 'bbb_a_seq' for SERIAL column 'bbb.a' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'bbb_pkey' for table 'bbb' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'bbb_a_key' for table 'bbb' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE mydb=> insert into AAA values (1); INSERT 20369 1 mydb=> insert into BBB values (1,1); INSERT 20370 1 mydb=> insert into BBB values (1,2); ERROR: Cannot insert a duplicate key into unique index bbb_a_key I would like that the pair keys (a,b) was unique. Certainly, I can remove unique index 'bbb_a_key'... But how more correctly? -- Marat Khairullin mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] âÅÓÐÌÁÔÎÁÑ ÐÏÞÔÁ http://mail.Rambler.ru/ òÁÍÂÌÅÒ-ðÏËÕÐËÉ http://ad.rambler.ru/ban.clk?pg=1691&bn=9346 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Quotes and spaces
I just upgraded to 7.1 and the query works for me now.
thanks,
Oleg
Stephan Szabo wrote:
> On Fri, 5 Oct 2001, Oleg Lebedev wrote:
>
> > Hi,
> > I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to
> > work in queries executed from Pl/pgSQl. Here is an example:
> >
> > create procedure get_name(varchar)
> > ...
> > BEGIN
> > query := ''SELECT first_name || || last_name FROM user'';
> > EXECUTE query;
> > ...
> > END;
> > ...
> >
> > Basically I want to get full name, i.e. first name separated with space from
> > the last name. If I follow the PL/pgSQL manual as shown above, I get parse
> > error.
> > What am I doing wrong?
>
> Odd, a script like the following works for me on both 7.1 and 7.2devel:
>
> drop table aa;
> drop function fgn(varchar);
> create table aa(a varchar);
> insert into aa values ('d');
> create function fgn(varchar) returns text as '
> DECLARE
> query text;
> rec record;
> BEGIN
> query := ''SELECT a || || a as bar from aa;'';
> RAISE NOTICE ''%'', query;
> FOR rec in EXECUTE query LOOP
> return rec.bar;
> END LOOP;
> END;'
> language 'plpgsql';
> select fgn('f');
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] COPY COMMAND -- Hanging
hi all, i am facing a strange problem in using COPY COMMAND. As i would like to merge 2 databases, i dumped data from one by $ pg_dump -R bhuvan -f bhuvan.sql Here i found data dumped using COPY COMMAND. now i tried to restore few of the tables. so i copied those tables alone to seperate file copy_commands.sql. then, i just tried this $ psql bhuvan -f copy_commands.sql ... i didnt get back with the result.. even none of the records have been copied. what could be the problem here? == Youth is when you blame all your troubles on your parents; maturity is when you learn that everything is the fault of the younger generation. == Regards, Bhuvaneswar. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Quotes and spaces
Hi, I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to work in queries executed from Pl/pgSQl. Here is an example: create procedure get_name(varchar) ... BEGIN query := ''SELECT first_name || || last_name FROM user''; EXECUTE query; ... END; ... Basically I want to get full name, i.e. first name separated with space from the last name. If I follow the PL/pgSQL manual as shown above, I get parse error. What am I doing wrong? thanks, Oleg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Why the weak key is created as unique
Example: mydb=> create table AAA (a serial primary key); NOTICE: CREATE TABLE will create implicit sequence 'aaa_a_seq' for SERIAL column 'aaa.a' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'aaa_pkey' for table 'aaa' CREATE mydb=> create table BBB (a serial references AAA, b integer, primary key(a,b)); NOTICE: CREATE TABLE will create implicit sequence 'bbb_a_seq' for SERIAL column 'bbb.a' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'bbb_pkey' for table 'bbb' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'bbb_a_key' for table 'bbb' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE mydb=> insert into AAA values (1); INSERT 20369 1 mydb=> insert into BBB values (1,1); INSERT 20370 1 mydb=> insert into BBB values (1,2); ERROR: Cannot insert a duplicate key into unique index bbb_a_key I would like that the pair keys (a,b) was unique. Certainly, I can remove unique index 'bbb_a_key'... But how more correctly? -- Marat Khairullin mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] âÅÓÐÌÁÔÎÁÑ ÐÏÞÔÁ http://mail.Rambler.ru/ òÁÍÂÌÅÒ-ðÏËÕÐËÉ http://ad.rambler.ru/ban.clk?pg=1691&bn=9346 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Why the weak key is created as unique
You probably do not want a serial in BBB since you want to be setting the values. Use "a int references AAA" instead I think. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Quotes and spaces
Oleg, I'm assuming that this is just a hypothetical example, as the below is far from the fastest way to get something as simple as a name. > BEGIN > query := ''SELECT first_name || || last_name FROM > user''; > EXECUTE query; > ... > END; > ... I'm pretty sure your quotes are correct. However, I believe "query" is a reserved word. Try using a different variable name. -Josh __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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Quotes and spaces
On Fri, 5 Oct 2001, Oleg Lebedev wrote:
> Hi,
> I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to
> work in queries executed from Pl/pgSQl. Here is an example:
>
> create procedure get_name(varchar)
> ...
> BEGIN
> query := ''SELECT first_name || || last_name FROM user'';
> EXECUTE query;
> ...
> END;
> ...
>
> Basically I want to get full name, i.e. first name separated with space from
> the last name. If I follow the PL/pgSQL manual as shown above, I get parse
> error.
> What am I doing wrong?
Odd, a script like the following works for me on both 7.1 and 7.2devel:
drop table aa;
drop function fgn(varchar);
create table aa(a varchar);
insert into aa values ('d');
create function fgn(varchar) returns text as '
DECLARE
query text;
rec record;
BEGIN
query := ''SELECT a || || a as bar from aa;'';
RAISE NOTICE ''%'', query;
FOR rec in EXECUTE query LOOP
return rec.bar;
END LOOP;
END;'
language 'plpgsql';
select fgn('f');
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] sql + C
hi.
who knows about C embebed (postgres + C)?
I have the program:
#include
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR base[50];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
EXEC SQL DECLARE C77 CURSOR FOR select datname from pg_user,pg_database
where usename= :user and datdba=usesysid;
main ()
{
EXEC SQL CONNECT TO mybase;
if(sqlca.sqlcode < 0)
{
printf(" error");
exit(1);
}
// now I want to get results
EXEC SQL OPEN C77;
EXEC SQL FETCH IN C77 INTO :base; // here, it's the problem, I can't to
get the result on the base variable. I think that can be the variable
type. then how should be the data type for ":base" variable?
...
...
.
...
.
.
.
.
pg_database has the attributes as follow:
mybase=> \d pg_database
Table "pg_database"
Attribute | Type | Modifier
---+-+--
datname | name| --->I can't to get the "datname"..why?
datdba| integer |
encoding | integer |
datpath | text|
I hope than you understand!
bye and thanks!
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] temporary views
Bruce Momjian wrote: > > > Hi > > I have simple question: How to create view on a temporary table? > > I need this, because pl/pgsql function returns data via temporary table. > > > > View created on a temporary table is useful only to the end of session. > > Next time i create the same temp table i get > > "Table xxx with oid xxx doesn't exist" > > Just name your temporary table the same name in every session. Why > bother with a view. Creating a view makes my life easier. My temporary table has fields like id1,id2,id3,id4 and view translates it using inner joins to name1,name2,name3,name4. This temp table has always the same name and I don't want to do the translation inside pl/pgsql function. Tomek ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL CONSTRAINTS - Constraining time values from two attributes on a table.
Chris, > Hi, > I was looking for a solution on how to write a constraint into a ' > create > table ' expression that would ensure that one ' TIME ' attribute > value > called arrival_time (declared as TIME) is allways constrained to have > a > value that ensures it is allways after another attribute value called > departure_time (declared as TIME). > Any know how this constraint would be written?? Easy: CONSTRAINT arr_dep_time CHECK ( arrival_time > departure_time ) __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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] temporary views
> Bruce Momjian wrote: > > > > > Hi > > > I have simple question: How to create view on a temporary table? > > > I need this, because pl/pgsql function returns data via temporary table. > > > > > > View created on a temporary table is useful only to the end of session. > > > Next time i create the same temp table i get > > > "Table xxx with oid xxx doesn't exist" > > > > Just name your temporary table the same name in every session. Why > > bother with a view. > > Creating a view makes my life easier. My temporary table has fields > like id1,id2,id3,id4 and view translates it using inner joins to > name1,name2,name3,name4. This temp table has always the same > name and I don't want to do the translation inside pl/pgsql function. OK, basically there is no way to create views reliably on temp tables: creattest=> create temp table x(y int); CREATE test=> create view z on x as select * from x; ERROR: parser: parse error at or near "on" test=> create view z as select * from x; CREATE test=> select * from z; y --- (0 rows) Of course this works, but exiting the session and restarting it gets you: test=> create temp table x(y int); CREATE test=> select * from z; ERROR: Relation "x" with OID 16562 no longer exists Internally, the problem is that the temp table is referenced by oid, not table name. If you create a temp in a later session, it doesn't have the same oid as the one in the session where you created the view. What actually should happen is that the view should go away at the end of the session. However, unlike indexes, we can have several tables involved in a view so it is hard to know exactly how to handle this. Seems like a TODO item, at least. What we could do is to create views as TEMP if they use temp tables and drop the view as soon as the session ends . You of course would have to recreate the view each time but because it is a _temp_ view, it could be done reliably by multiple backends at the same time. Added to TODO: * Allow views on temporary tables to behave as temporary views -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] temporary views
On Fri, 5 Oct 2001, Bruce Momjian wrote: > > What actually should happen is that the view should go away at the end > of the session. However, unlike indexes, we can have several tables > involved in a view so it is hard to know exactly how to handle this. > > Seems like a TODO item, at least. What we could do is to create views > as TEMP if they use temp tables and drop the view as soon as the session > ends . You of course would have to recreate the view each time but > because it is a _temp_ view, it could be done reliably by multiple > backends at the same time. Didn't someone suggest dropping saving of parsed plans with OIDs altogether, and saving the underlying query instead? The point was that parser and planner are fast enough to make it unnecessary to save plans. I don't remember what was disposition of that idea -alex ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL CONSTRAINTS - Constraining time values from two
Maher, Christopher W writes:
> I was looking for a solution on how to write a constraint into a ' create
> table ' expression that would ensure that one ' TIME ' attribute value
> called arrival_time (declared as TIME) is allways constrained to have a
> value that ensures it is allways after another attribute value called
> departure_time (declared as TIME).
create table test (
arrival_time time,
departure_time time,
check (arrival_time < departure_time)
);
insert into test values ('12:00', '14:30');
INSERT 20651 1
insert into test values ('12:00', '9:45');
ERROR: ExecAppend: rejected due to CHECK constraint $1
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(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] Simple Query HELP!!!
[EMAIL PROTECTED] (--CELKO--) wrote in message news:<[EMAIL PROTECTED]>... > Please write DDL and not narrative. here is my guess at what you are > trying to do. What you posted was not a table because you had no key. > TEXT is not the datatype to use for names -- unless they are thousand > of characters long!! > Recording age as an integer is useless -- give us the birthday and we > can always compute their age. Is this what you meant to post? > I didnt give DDL because was to abstract to explain, this is just an example, i did translate the query (really was a subquery in a IN ) and it work... dont care about the data types, I was interest only in the relations ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem with n to n relation
Hi, i create n to n relations like this, right? create table person ( idserial, name text ); create table address ( id serial, street text ... ); create table person2adress ( id serial, person_id integer not null references person(id), address_id integer not null references address(id), ); than i can select all adresses from one person with id =1 with select street from address where id = ( select adress_id from person2adress where person_id = 1 ); ok so far so good. but you can still insert persons without any adress. so its a 0..n relation. But how van i achieve that you can´t insert any person without adress??? thanks in advance janning ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problem with n to n relation
just get rid of the serial_id in person2adress -- there is no reason for it. Make the pk of that table a composite --> person_id, address_id <-- that way you have added some additional integrity to your structure. Only one record can exist ffor a given person at a given address. However any person can have any number of address and any address can have any number of people living at it. Morgan At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote: >Hi, > >i create n to n relations like this, right? > >create table person ( > idserial, > name text >); > >create table address ( > id serial, > street text > ... >); > >create table person2adress ( > id serial, > person_id integer not null references person(id), > address_id integer not null references address(id), >); > >than i can select all adresses from one person with id =1 with >select street >from address >where id = > ( > select adress_id > from person2adress > where person_id = 1 > ); > >ok so far so good. but you can still insert persons without any >adress. so its a 0..n relation. But how van i achieve that you can´t >insert any person without adress??? > >thanks in advance >janning > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster - Morgan Curley Partner, e4media [EMAIL PROTECTED] 917 751 8328 http://www.e4media.com - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] COPY COMMAND -- Hanging
hi all, i am facing a strange problem in using COPY COMMAND. As i would like to merge 2 databases, i dumped data from one by $ pg_dump -R bhuvan -f bhuvan.sql Here i found data dumped using COPY COMMAND. now i tried to restore few of the tables. so i copied those tables alone to seperate file copy_commands.sql. then, i just tried this $ psql bhuvan -f copy_commands.sql ... i didnt get back with the result.. even none of the records have been copied. what could be the problem here? == Youth is when you blame all your troubles on your parents; maturity is when you learn that everything is the fault of the younger generation. == Regards, Bhuvaneswar. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] SQL-Programmer tool and field%type support
Hi, I'm looking for PL/SQL programmer tool. I wanted to use SQL-Programmer but it doesn't support Postgres data base. Please can sommeone suggest me an other tool wich can replace this one or explaine me how we can use SQLProgrammer with Postgres if it is possible. I have an other question about the use of %type and alias in PL/SQL. So the excution of functions containing varibales of type maytable.field1%type prompts parser errors. error on the creation of the function : create function test (int) returns maytable.field1%type as' ... parser: parse error at or near "." error on the excution of the function : create function test (int) returns int as' declare var1 maytable.field1%type; var2 alias for $1; parser: parse error at or near '%' parser: parse error at or near "$1" Please help me. Thanks. Mourad. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL CONSTRAINTS - Constraining time values from two attributes on a table.
Hi, I was looking for a solution on how to write a constraint into a ' create table ' expression that would ensure that one ' TIME ' attribute value called arrival_time (declared as TIME) is allways constrained to have a value that ensures it is allways after another attribute value called departure_time (declared as TIME). Any know how this constraint would be written?? Any help on this would be much appreciated, Thanks, Chris. ---(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] ORDER BY case insensitive?
Thanks for the suggestion, Jason. A co-worker of mine, however, had this response: Yes, but my guess is that that will first convert all million (or whatever) records to upper case, and then physically sort them. It won't be able to make use of the index. To make this efficient, do we need to uppercase all of the data before adding to the table? (yuk) - Bob - Original Message - From: "Jason Earl" <[EMAIL PROTECTED]> To: "Bob Swerdlow" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, October 04, 2001 3:47 PM Subject: Re: [SQL] ORDER BY case insensitive? > SELECT * FROM MyTable ORDER BY lower(Name); > > Should do the trick. > > Jason Earl > > --- Bob Swerdlow <[EMAIL PROTECTED]> wrote: > > How do I get the rows sorted in a case insensitive > > way? > > > > I have some queries that basically fit the form: > > SELECT * FROM MyTable ORDER BY Name; > > When I view the results, all of the Name's that > > start with an upper case > > letter precede all of the Name's that start with a > > lower case letter. I > > want them all in alphabetic order, regardless of > > case. > > > > I've looked in PostgreSQL Introduction and Concepts > > by Bruce Momjian and in > > the FAQ at > > http://postgresql.overtone.org/docs/faq-english.html > > > > Thanks for your help. > > > > -- > > Bob Swerdlow > > Chief Operating Officer > > Transpose, LLC > > [EMAIL PROTECTED] > > > > > > > > ---(end of > > broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > __ > Do You Yahoo!? > NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. > http://geocities.yahoo.com/ps/info1 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] getting OID from PL/PgSQL AFTER trigger
Howdy, I'm trying to get the OID of the NEW/OLD rows in an AFTER trigger implemented in PL/PgSQL. Unfortunately, those end up as records in the trigger, and I can't just use NEW.oid. Is there any way to do this? I could do it using a trigger written in C, but I'd rather not have to carry around a C .so just for this. Note that I effectively have no knowledge of the format of the record that's passed in new/old, so I can't either add a sequence or use some already existing primary key to identify the row uniquely. One quick hack is to "INSERT INTO dummy VALUES (NULL);" and then GET DIAGNOSTICS z = RESULT_OID; and then use z - 1, but that will get me into lots of trouble with multiple cilents accessing the database. :-) For the time being, I'm going to implement the trigger in C, but I'm open to suggestions on how to implement this in straight PL/PgSQL. Thanks in advance, - Vlad ---(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] challenging query
> > Consider the following table: > > A B C D select? > --- > 1 FOO A1 100 n > 1 BAR Z2 100 n > 2 FOO A1 101 y > 2 BAR Z2 101 y > 3 FOO A1 102 y > 4 BAR Z2 99 y > 5 FOO A1 99 n > 6 BAR Z2 98 n > 7 FOO AB 103 y > 7 BAR ZY 103 y > > This table has the idea of "groups", that is, a group is defined as > all of the words from B that have the same number A. The values in > column C also matter- we want to select both groups A=7 and A=1 since > they contain different values C. Note that the groups defined by A=1 > and A=3 are distinct- they do not contain the same number of words > from B, so we want to select them both. Also note that D is datetime, > and all the rows with the same number A will have the same D (this is > actually ensured by a single row in another table.) > > I want to select all of the numbers A which define distinct groups and > have the highest datetime D. Is this possible in a SQL query? > Now that I've read your request more attentively, I understand what you want. But I have to admit I have no idea how to word the query, I even don't know if it's possible at all. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
