Re: [SQL] Problem with n to n relation
Am Freitag, 5. Oktober 2001 14:30 schrieb Morgan Curley: > 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. ok fine, i understood it after i figured out what pk means :-) but how do i reach my goal. It should not be allowed to have a person without any address?? janning > At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote: > >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??? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Search by longitude/latitude
> Hi all, > > I need to implement "Find all hospitals in a 5 mile radius". Say I have all > the coordinates on them stored in a table with the fields longitude and > latitude. Has anybody some sample code for that? > > Best regards, > Chris > Here's a plpgsql function that will do what you need. You might also look at the earthdistance code in contrib if you'd rather have a C function. HTH, Joe geodist.sql Description: Binary data ---(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] SQL-Programmer tool and field%type support
Mourad, > > 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' ... What Roberto is saying is that the declaration: DECLARE v_field1 mytable.field1%type is supported internal to a PL/pgSQL function, but CREATE FUNCTION test1(int) RETURNS mytable.filed1%type is not. This is because the %type declaration is supported in PL/pgSQL, but not in Postgres SQL. -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]
[SQL] ROUND function ??
Hi, select ROUND(0.5) ; returns 0 select ROUND(1.5) ; returns 2; select ROUND(2.5) ; returns 2; select ROUND(3.5) ; returns 4;so on . I'm sure you would have figured out what's happening !!! Why ?? How do I get to approximate any number x.5 as x+1 ?? Saurabh
[SQL] How to Return Unique Elements From An Array?
hello all, how can we get unique elements from an array(of any type)? Regards, Bhuvaneswar. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sql + C
I'm having some experience with C embedded SQL
and what I can see at first sight is
1) DECLARE CURSOR statement - missing colon:
EXEC SQL DECLARE C77 CURSOR FOR select datname from pg_user,pg_database
where usename= :user and datdba= :usesysid;
2) before OPEN you have to declare and set the where clause variables:
user and usesysid are not declared nor set
3) VARCHAR base[50]:
is probably not the right type, because VARCHAR always uses a 4 byte
header to determine the length, whereas the name type is of 32 byte
fixed length. A simple char name[32] should do it.
If the problem persists, you should inspect the sqlca structure
after every step of EXEC SQL execution for more information on
what's going wrong.
By the way, I'm porting an application to PostgreSQL and I have
decided to get rid off embedded SQL completely, because I feel
much more comfortable with the functions the libpq - C Library
package is providing. Especially, you never again have to worry
about data types, because the PQgetvalue function retrieves
everything as string.
Regards, Christoph
>
> #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|
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] select 5/2???
The good select is SELECT 5./2. BUT select -5./2. +--+ | ?column? | +--+ | 2.5 | not -2.5 +--+ Michel BELLON LCIE - Informatique appliquée 33 (0)1 40 95 60 35 > -Message d'origine- > De: guard [SMTP:[EMAIL PROTECTED]] > Date: mercredi 3 octobre 2001 19:05 > À:[EMAIL PROTECTED] > Objet:select 5/2??? > > dear all > > I run select 5/2 = 2 > who to get "2.5" > > thanks > > > -- > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(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] select 5/2???
On Mon, 08 Oct 2001 22:31, BELLON Michel wrote: Works ok for me. chris=# select -5/2.0; ?column? -- -2.5 (1 row) chris=# select version(); version - PostgreSQL 7.1.1 on i586-pc-linux-gnu, compiled by GCC 2.96 (1 row) > The good select is > > SELECT 5./2. > > BUT > > select -5./2. > +--+ > > | ?column? | > > +--+ > > | 2.5 | not -2.5 > > +--+ > > > > Michel BELLON > LCIE - Informatique appliquée > 33 (0)1 40 95 60 35 > > > -Message d'origine- > > De: guard [SMTP:[EMAIL PROTECTED]] > > Date: mercredi 3 octobre 2001 19:05 > > À: [EMAIL PROTECTED] > > Objet: select 5/2??? > > > > dear all > > > > I run select 5/2 = 2 > > who to get "2.5" > > > > thanks > > > > > > -- > > > > > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] select 5/2???
OK the problem is with ZEOS Components Michel BELLON LCIE - Informatique appliquée 33 (0)1 40 95 60 35 > -Message d'origine- > De: Christopher Sawtell [SMTP:[EMAIL PROTECTED]] > Date: lundi 8 octobre 2001 12:39 > À:BELLON Michel; guard; [EMAIL PROTECTED] > Objet:Re: [SQL] select 5/2??? > > On Mon, 08 Oct 2001 22:31, BELLON Michel wrote: > Works ok for me. > > chris=# select -5/2.0; > ?column? > -- > -2.5 > (1 row) > > chris=# select version(); >version > - > PostgreSQL 7.1.1 on i586-pc-linux-gnu, compiled by GCC 2.96 > (1 row) > > > The good select is > > > > SELECT 5./2. > > > > BUT > > > > select -5./2. > > +--+ > > > > | ?column? | > > > > +--+ > > > > | 2.5 | not -2.5 > > > > +--+ > > > > > > > > Michel BELLON > > LCIE - Informatique appliquée > > 33 (0)1 40 95 60 35 > > > > > -Message d'origine- > > > De: guard [SMTP:[EMAIL PROTECTED]] > > > Date: mercredi 3 octobre 2001 19:05 > > > À:[EMAIL PROTECTED] > > > Objet:select 5/2??? > > > > > > dear all > > > > > > I run select 5/2 = 2 > > > who to get "2.5" > > > > > > thanks > > > > > > > > > -- > > > > > > > > > > > > > > > ---(end of > broadcast)--- > > > TIP 5: Have you checked our extensive FAQ? > > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > ---(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] temporary views
Bruce Momjian wrote: > > We can handle the temp views thing two ways, either allow views to map > to temp tables by name, or allow temp views to map to temp tables that > exist at creation time and drop the views on session exit. The original > poster clearly wanted the first behavior, but I agree with Peter that > the second has fewer surprises for the user and is more standard. I think, that referring tables by names would be enough. I found another problem connected to this. There is something like this in documetation: "Existing permanent tables with the same name are not visible (in this session) while the temporary table exists." Why not to overlap permanent table? Currently it doesn't work: create table x (a integer); create view y as select * from x; select * from y; OK create temp table x as select * from x; select * from y; ERROR: Relation "x" with OID 364752 no longer exists > I have updated the TODO item to: > > * Allow temporary views Nice to hear it. > It would be interesting of plpgsql could try for an table match by oid > first, and if that fails, try a match by table name and match only if a > temp table is hit. So basically the only table-name matching that would > happen would be hits on temp tables. But why only plpgsql? Would it be difficult to add it to SQL implementation of PostgreSQL? Tomek ---(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] PEAR Problem
Hi, It's me again. I have been able to solve most of the porting problems from mysql to pgsql. But I have got struck in one place. I have a problem where PEAR's associative array doesn't recognise the mix case letters. eg . I issue a query through PEAR db and get results using fetchRow of PEAR. Now I have set associative array feature ON. suppose the query is select NetCode,NetworkName from NetworkTab; this would return the result into a variable called $dbRow to echo the contents returned by the pgsql, I have to give $dbRow[NetCode] and $dbRow[NetworkName] This used to work perfectly fine with mysql, but as I moved to pgsql, PEAR started to return nothing like if i echo $dbRow[NetCode] it prints nothing but in the same echo is I change it echo $dbRow[netcode], it prints the value of the Network Code. How do I handle this situation. The application is fully written with Mix Case letters for the database fields and returned result set. And one more thing "SET AUTOCOMMIT=0" which is to set auto commiting to "No" in mysql doesn't work in pgsql what is the equivalent command. -- Best regards, Gurudutt mailto:[EMAIL PROTECTED] Life is not fair - get used to it. Bill Gates ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Search by longitude/latitude
Hi all, I need to implement "Find all hospitals in a 5 mile radius". Say I have all the coordinates on them stored in a table with the fields longitude and latitude. Has anybody some sample code for that? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL Syntax / Logic question
To help you understand SQL I should point out that your version would work (assuming only 1 school per person) if you just left the friends out of the FROMs for the sub-selects:- select frienda, friendb from friends where (select schools.school from schools as schoolsa where friends.frienda = schools.person) = (select schools.school from schools as schoolsb where friends.friendb = schools.person); This is because the only thing that the sub-selects need to know from friends is the person to retrieve for, and that comes from the WHERE clauses. Although what Josh said is correct, and his is a better solution as it can cope with the 2+ schools per person problem. Josh Berkus wrote: > snip... > Mike, > > > select frienda, friendb from friends where (select > > schools.school from friends,schools where friends.frienda = > > schools.person) = (select schools.school from friends,schools where > > friends.friendb = schools.person); > > Too complicated. You need to learn how to use JOINS and table aliases > (or find yourself some friends who know SQL!): > > SELECT friends.frienda, friends.friendb > FROM friends JOIN schools schoola ON friends.frienda = schoola.person > JOIN schools schoolb ON friends.friendb = schoolb.person > WHERE schoola.school = schoolb.school > > and, if it's possible that any particular person went to more than one > school, add: > > GROUP BY frienda, friendb > > Simple, neh? > > -Josh snip.. -- This is the identity that I use for NewsGroups. Email to this will just sit there. If you wish to email me replace the domain with knightpiesold . co . uk (no spaces). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Search by longitude/latitude
Here is some perl code which does what you want. - Original Message - From: "Chris Ruprecht" <[EMAIL PROTECTED]> To: "pgsql" <[EMAIL PROTECTED]> Sent: Monday, October 08, 2001 8:58 AM Subject: [SQL] Search by longitude/latitude > Hi all, > > I need to implement "Find all hospitals in a 5 mile radius". Say I have all > the coordinates on them stored in a table with the fields longitude and > latitude. Has anybody some sample code for that? > > Best regards, > Chris > > > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > distance Description: Binary data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Search by longitude/latitude
Better still, check out PostGIS at http://postgis.refractions.net/ Cheers, Colin ---(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 to Return Unique Elements From An Array?
Have you tried already SELECT DISTINCT FROM ; If you receive an error message like Unable to identify an ordering operator ... Refer to 'Server Programming' section Extending SQL: Operators to learn about creating your own operators for array comparison Or search the mailing-list for similar requests. Regards, Christoph > > hello all, > > how can we get unique elements from an array(of any type)? > > Regards, > Bhuvaneswar. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] temporary views
> Bruce Momjian wrote: > > > > We can handle the temp views thing two ways, either allow views to map > > to temp tables by name, or allow temp views to map to temp tables that > > exist at creation time and drop the views on session exit. The original > > poster clearly wanted the first behavior, but I agree with Peter that > > the second has fewer surprises for the user and is more standard. > I think, that referring tables by names would be enough. I found another > problem connected to this. There is something like this in documetation: > "Existing permanent tables with the same name are not visible > (in this session) while the temporary table exists." Why not to overlap > permanent table? Currently it doesn't work: > > create table x (a integer); > create view y as select * from x; > select * from y; > OK > create temp table x as select * from x; > select * from y; > ERROR: Relation "x" with OID 364752 no longer exists Yes, we could add code that tried the temp table first, and if it didn't match the oid, fall back to the permanent table. Of course, it would break the temp table overlap rules. Of course, there is the question of whether it is worth doing this. If you create the view after the temp table is created it would properly map to the temp table. If you have created a temp table that masks the real table, maybe you want the view to fail. Temp tables masking real tables is already pretty powerful and mapping some fallback rules on top of this seems a little too powerful and perhaps a little too confusing. > > It would be interesting of plpgsql could try for an table match by oid > > first, and if that fails, try a match by table name and match only if a > > temp table is hit. So basically the only table-name matching that would > > happen would be hits on temp tables. > But why only plpgsql? Would it be difficult to add it to SQL > implementation > of PostgreSQL? Yes, it would be done there too. -- 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 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] Problem with n to n relation
On Mon, 8 Oct 2001, Janning Vygen wrote: > Am Freitag, 5. Oktober 2001 14:30 schrieb Morgan Curley: > > 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. > > ok fine, i understood it after i figured out what pk means :-) > > but how do i reach my goal. It should not be allowed to have a person > without any address?? Hmm, do you always have at least one known address at the time you're inserting the person? I can think of a few somewhat complicated ways. Person getting a column that references person2adress with initially deferred, the problem here is that you don't know one of the tables' serial values unless you're selecting it yourself which would mean you'd have to change how you were getting your incrementing numbers (getting currval of some sequence presumably and using that to insert into person2adress). You could probably also make your own deferred constraint trigger (although I'm not sure that it's documented since I don't think it was really meant as a user feature) which does the check at the end of any transaction in which rows were inserted into person. > > At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote: > > > >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??? ---(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] Problem with n to n relation
Am Montag, 8. Oktober 2001 18:09 schrieb Stephan Szabo:
> On Mon, 8 Oct 2001, Janning Vygen wrote:
>
> > but how do i reach my goal. It should not be allowed to have a
> > person without any address??
>
> Hmm, do you always have at least one known address at the time
> you're inserting the person?
>
> I can think of a few somewhat complicated ways. Person getting a
> column that references person2adress with initially deferred, the
> problem here is that you don't know one of the tables' serial
> values unless you're selecting it yourself which would mean you'd
> have to change how you were getting your incrementing numbers
> (getting currval of some sequence presumably and using that to
> insert into person2adress).
yeah, thats a way which works. dont know if its cool to do it like
this, but you cant insert a person without any address. so you are
forced to use a transaction.
create table person (
id serial,
name text
);
create table address (
id serial,
street text NOT NULL
);
create table person2address (
id int4,
address_id int4 NOT NULL REFERENCES address (id),
person_id int4 NOT NULL REFERENCES person (id)
);
ALTER TABLE person ADD CONSTRAINT person_has_adress FOREIGN KEY (id)
REFERENCES person2address(id) DEFERRABLE INITIALLY DEFERRED;
begin;
insert into person (name) values ('janning');
insert into address (street) values ('Sesamestreet');
insert into person2address values(1,1,1);
commit;
> You could probably also make your own deferred constraint trigger
> (although I'm not sure that it's documented since I don't think it
> was really meant as a user feature) which does the check at the end
> of any transaction in which rows were inserted into person.
>
> > > At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote:
> > > >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),
> > > >);
> > > >
--
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf
fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problem with n to n relation
On Mon, 8 Oct 2001, Janning Vygen wrote:
> Am Montag, 8. Oktober 2001 18:09 schrieb Stephan Szabo:
> > On Mon, 8 Oct 2001, Janning Vygen wrote:
> >
> > > but how do i reach my goal. It should not be allowed to have a
> > > person without any address??
> >
> > Hmm, do you always have at least one known address at the time
> > you're inserting the person?
> >
> > I can think of a few somewhat complicated ways. Person getting a
> > column that references person2adress with initially deferred, the
> > problem here is that you don't know one of the tables' serial
> > values unless you're selecting it yourself which would mean you'd
> > have to change how you were getting your incrementing numbers
> > (getting currval of some sequence presumably and using that to
> > insert into person2adress).
>
> yeah, thats a way which works. dont know if its cool to do it like
> this, but you cant insert a person without any address. so you are
> forced to use a transaction.
>
> create table person (
> id serial,
> name text
> );
>
> create table address (
> id serial,
> street text NOT NULL
> );
>
> create table person2address (
> id int4,
> address_id int4 NOT NULL REFERENCES address (id),
> person_id int4 NOT NULL REFERENCES person (id)
> );
>
> ALTER TABLE person ADD CONSTRAINT person_has_adress FOREIGN KEY (id)
> REFERENCES person2address(id) DEFERRABLE INITIALLY DEFERRED;
This unfortunately will fail on 7.1 and higher due to the fact that
a target of a foreign key constraint must have a unique constraint
on it. The problem is that if you make id effectively the same
as person's id and unique you can't have two addresses for one person.
I think you might need to do something like (untested and I think I got
some syntax confused, but enough for the idea)
create table person (
id serial,
name text,
foo int4
);
create table address(
id serial,
street text NOT NULL
);
create table person2address (
id int4,
address_id int4 NOT NULL REFERENCES address (id),
person_id int4 NOT NULL REFERENCES person (id)
);
create sequence person2address_seq;
ALTER TABLE person ADD CONSTRAINT person_has_adress FOREIGN KEY (id)
REFERENCES person2address(id) DEFERRABLE INITIALLY DEFERRED;
begin;
select next_val('person2address_seq');
-- I'll refer to this as below
insert into person (name, foo) values ('janning', );
insert into address (street) values ('Sesamestreet');
insert into person2address values (, currval('person_id_seq'),
currval('address_id_seq'));
commit;
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL-Programmer tool and field%type support
On Fri, Oct 05, 2001 at 03:37:14PM +0200, Mourad EL HADJ MIMOUNE wrote: > 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 don't know such tool. The documentation has some advices on tools/ways to program in PL/pgSQL. > 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' ... This syntax is not supported in PG 7.1. AFAIK, it will be supported in PG 7.2. You can use function overloading if you need the same function to return different types. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Information is the greatest weapon of power to the modern wizard. ---(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] SQL-Programmer tool and field%type support
"Josh Berkus" <[EMAIL PROTECTED]> writes: > DECLARE > v_field1 mytable.field1%type > is supported internal to a PL/pgSQL function, but > CREATE FUNCTION test1(int) RETURNS mytable.filed1%type > is not. This is because the %type declaration is supported in PL/pgSQL, > but not in Postgres SQL. It does work in 7.2devel, however ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ROUND function ??
"Saurabh Mittal" <[EMAIL PROTECTED]> writes: > select ROUND(0.5) ; returns 0 > select ROUND(1.5) ; returns 2; > select ROUND(2.5) ; returns 2; > select ROUND(3.5) ; returns 4;so on .=20 > I'm sure you would have figured out what's happening !!! Why ?? Because the IEEE float math standard says so. Round-to-nearest-even is considered good practice. > How do I get to approximate any number x.5 as x+1 ?? Try FLOOR(x + 0.5) if you really want the other behavior. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ROUND function ??
> select ROUND(0.5) ; returns 0 > select ROUND(1.5) ; returns 2; > select ROUND(2.5) ; returns 2; > select ROUND(3.5) ; returns 4;so on . > I'm sure you would have figured out what's happening !!! Why ?? > How do I get to approximate any number x.5 as x+1 ?? Looks like a bug to me: test=# select * from pg_proc where proname = 'round'; proname | proowner | prolang | proisinh | proistrusted | proiscachable | proisstrict | pronargs | proretset | prorettype | proargtypes | probyte_pct | properbyte_cpu | propercall_cpu | prooutin_ratio | prosrc | probin -+--+-+--+--+---+--- --+--+---++-+-+- ---++++- --- round |1 | 12 | f| t| t | t |1 | f |701 | 701 | 100 | 0 | 0 |100 | dround | - round |1 | 14 | f| t| t | t |1 | f | 1700 |1700 | 100 | 0 | 0 |100 | select round($1,0) | - round |1 | 12 | f| t| t | t |2 | f | 1700 | 1700 23 | 100 | 0 | 0 |100 | numeric_round | - (3 rows) test=# select round(2.5); round --- 2 (1 row) test=# select round(2.5,0); round --- 3 (1 row) test=# select round(2.5::numeric); round --- 3 (1 row) When doing "select round(2.5)" the 2.5 gets cast as a float and the "dround" function is used. When doing "select round(2.5,0)", or "select round(2.5::numeric)", the 2.5 gets cast as a numeric and the function "numeric_round" is used, producing a different result. It looks like "dround" simply calls the rint system function, so I'd guess the issue is really there (and maybe platform dependent?). I do recall at least one interpretation of rounding that calls for rounding a 5 to the even digit (ASTM), so the rint behavior may not be strictly speaking a bug -- but certainly having two different interpretations is. In any case, use "select round(2.5,0)" for now. Hope this helps, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
