Re: [SQL] returning a recordset from PLpg/SQL
Tom Lane wrote:
> Terence Kearns <[EMAIL PROTECTED]> writes:
>
>>I tried
>>RETURNS SETOF RECORD
>>but that doesn't work
>
>
> Sure it does, if you use it correctly. Better show us what you did.
>
>regards, tom lane
Well I haven't yet done anything because I couldn't get anything to
compile which returned SETOF RECORD..
I'll write some code which I would like to work.
Here's the relevent part of the schema:
(see attached gif for full diagram)
CREATE DOMAIN docs.context
AS varchar(32)
NOT NULL;
CREATE TABLE docs.documents
(
doc_id int4 NOT NULL DEFAULT nextval('docs.seq_docs_doc_id'::text),
doc_title varchar(256) NOT NULL,
doc_summary varchar(512),
doc_folder_id int4 NOT NULL,
doc_sort_index int4,
CONSTRAINT pkey_docs PRIMARY KEY (doc_id),
CONSTRAINT fkey_documents_folder_id FOREIGN KEY (doc_folder_id)
REFERENCES docs.folders (folder_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT unique_doc_id_folder_id UNIQUE (doc_id, doc_folder_id)
) WITHOUT OIDS;
COMMENT ON TABLE docs.documents IS 'This table records a LOGICAL
document (as opposed to it\'s physical incarnation). Metadata plus extra
attribute_values are recorded against this entity. Details regarding
physical manifestations of these documents are contained in the
document_instance table which might contain versions of the same
document, but in different formats or in different locations (mirrors).
Versioning information is also kept as a field in the document instance
table (and not in a dedicated document version table for reasons of
performance and simplicity).';
CREATE TABLE docs.attribute_profiles
(
att_profile_id int4 NOT NULL DEFAULT
nextval('docs.seq_attribute_profiles_att_profile'::text),
att_profile_title varchar(128) NOT NULL,
att_profile_description text,
att_profile_context docs.context,
CONSTRAINT pkey_attribute_profiles PRIMARY KEY (att_profile_id),
CONSTRAINT fkey_att_profile_context FOREIGN KEY (att_profile_context)
REFERENCES docs.contexts (context) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT unique_att_profile_title_context UNIQUE
(att_profile_title, att_profile_context)
) WITHOUT OIDS;
COMMENT ON TABLE docs.attribute_profiles IS 'This table keeps a list of
entity attribute collections. This list is divided into application
contexts. The application can then select from a list of profiles in
it\'s own context and use each profile to build the data entry form.
Form building is the main usage of this table. Insert and lookup of user
data does not require this table. The attribute_value table(s) is used
for that.';
CREATE TABLE docs.entity_attributes
(
att_id int4 NOT NULL DEFAULT
nextval('docs.seq_entity_attribute_id'::text),
att_profile_id int4 NOT NULL,
att_name varchar(128) NOT NULL,
att_sort_index int4,
att_data_type varchar(32) NOT NULL,
att_is_metadata bool NOT NULL DEFAULT false,
CONSTRAINT pkey_entity_attributes PRIMARY KEY (att_id),
CONSTRAINT fkey_entity_attribute_profile_id FOREIGN KEY
(att_profile_id) REFERENCES docs.attribute_profiles (att_profile_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT unique_att_name_profile_id UNIQUE (att_name, att_profile_id),
CONSTRAINT check_entity_att_data_type CHECK att_data_type::text =
'timestamp'::text OR att_data_type::text = 'varchar'::text OR
att_data_type::text = 'text'::text OR att_data_type::text = 'integer'::text
) WITHOUT OIDS;
COMMENT ON TABLE docs.entity_attributes IS 'This table contains a list
of user-defined (developer defined) entity fields/attributes. It is used
to facilitate document entity exstensibility at runtime. In other words,
you can add fields to the document information form without ALTERing the
physical data structure of this schema. Corresponding user-data is not
stored here, but in the attribute_value table(s). IMPORTANT: see
comments on the att_data_type field.';
CREATE TABLE docs.document_attribute_values
(
entity_att_id int4 NOT NULL,
doc_id int4 NOT NULL,
value_timestamp timestamp,
value_varchar varchar(256),
value_text text,
value_integer int4,
CONSTRAINT pkey_document_attribute_values PRIMARY KEY (entity_att_id,
doc_id),
CONSTRAINT fkey_document_attribute_values_att_id FOREIGN KEY
(entity_att_id) REFERENCES docs.entity_attributes (att_id) ON UPDATE
RESTRICT ON DELETE RESTRICT,
CONSTRAINT fkey_document_attribute_values_doc_id FOREIGN KEY (doc_id)
REFERENCES docs.documents (doc_id) ON UPDATE RESTRICT ON DELETE RESTRICT
) WITHOUT OIDS;
COMMENT ON TABLE docs.document_attribute_values IS 'This table contains
user entries for document entity_attributes. The att_data_type field
contained in the entity_attributes table identified by value_att_id
points to the actual column in this table which ultimately stores the
target data. In other words, apart from the entity_att_id and the doc_id
field, only one out of the other fields is ever populated.';
So here's the desired function:
(of course, there's probably a who
Re: [SQL] returning a recordset from PLpg/SQL
On Tue, 2 Mar 2004, Terence Kearns wrote: > Tom Lane wrote: > > Terence Kearns <[EMAIL PROTECTED]> writes: > > > >>I tried > >>RETURNS SETOF RECORD > >>but that doesn't work > > > > > > Sure it does, if you use it correctly. Better show us what you did. > > > >regards, tom lane > > Well I haven't yet done anything because I couldn't get anything to > compile which returned SETOF RECORD.. As a starting point, SETOF "RECORD" is different from SETOF RECORD given PostgreSQL's fold case to lower case for unquoted names. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] User defined types -- Social Security number...
I would represent an SSN as numeric(9,0). an int 32 would work though. 2**31 is > 9 On Sun, 29 Feb 2004, Christopher Browne wrote: > The world rejoiced as [EMAIL PROTECTED] (Michael Chaney) wrote: > > Look, you're thinking way too hard on this. An SSN is a 9-digit number, > > nothing more. There are some 9-digit numbers which aren't valid SSN's, > > and you might want to get fancy and create a constraint for that. > > > > Regardless, you are making a *major* mistake of confusing data > > storage with rendering. It is common to *render* an SSN as > > xxx-xx- and its cousin the FETID (Federal Employers Tax ID) as > > xx-xxx. To store the dashes makes no sense. They're in the > > same place each time, it's wasted data. > > > > Store the SSN as an "integer". When you begin to think about this > > correctly, the "leading zeros" problem disappears since that is also a > > *rendering* issue. > > Well put. > > The one thing that is a bit unfortunate is that 32 bit ints aren't > quite big enough for this. You need 1 extra digit :-(. > > > When you pull the data out, either fix it up in your programming > > language to the format that you wish, or use the to_char function as > > shown above in your select statements. > > Using a view to hide the "physical" representation is also an idea. > > A full scale type definition could make for an even more efficient > approach that makes the implementation appear invisible. > > > To help you think about this whole issue, consider the timestamp > > datatype. Timestamps are stored as a Julian date internally. I > > suspect that they use a double-floating point as the actual format, > > but regardless the point is that it's a number. Rather than storing > > Actually, it's an "int64"; a 64 bit integer, on platforms that support > that type. It's a "double" only on platforms that do not support that > type. > > > It's easier to use that as a basic format from which we can render > > it in any way we wish. > > Indeed. > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] returning a recordset from PLpg/SQL
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Tue, 2 Mar 2004, Terence Kearns wrote: >> Well I haven't yet done anything because I couldn't get anything to >> compile which returned SETOF RECORD.. > As a starting point, SETOF "RECORD" is different from SETOF RECORD given > PostgreSQL's fold case to lower case for unquoted names. Also, you can hardly expect a function to return a rowtype that doesn't even exist until the function executes --- how the heck is the parser supposed to make sense of the calling query? So the "execute create type" part of this is nonsense, I'm afraid. The SETOF RECORD mechanism will let you return a rowtype that is not known fully at the time the function is written, but the rowtype does have to be known when the calling query is parsed. You might be able to replace the CREATE TYPE with an anonymous record type in the calling query: select ... from details_for_profile(...) as x(doc_id int4, doc_title varchar(256), ...); regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] User defined types -- Social Security number...
On Monday 01 March 2004 8:54 am, Dana Hudes wrote: > I would represent an SSN as numeric(9,0). > an int 32 would work though. > 2**31 is > 9 > > On Sun, 29 Feb 2004, Christopher Browne wrote: > > The world rejoiced as [EMAIL PROTECTED] (Michael Chaney) wrote: > > > Look, you're thinking way too hard on this. An SSN is a > > > 9-digit number, nothing more. There are some 9-digit numbers > > > which aren't valid SSN's, and you might want to get fancy and > > > create a constraint for that. > > > > > > Regardless, you are making a *major* mistake of confusing data > > > storage with rendering. It is common to *render* an SSN as > > > xxx-xx- and its cousin the FETID (Federal Employers Tax ID) > > > as xx-xxx. To store the dashes makes no sense. They're in > > > the same place each time, it's wasted data. I missed the start of this thread but will chime in with a comment anyway. My rule is to select an appropriate numeric type of data if you will be doing numeric types of things to it, character types if you will be doing character manipulations, etc. I don't know of any good reasons to need to know SSN/6.9, sqrt(SSN), SSN+7.86 but there are plenty of good reasons to need the first three characters (the "area number"), the middle two characters (the "group number", and the last 4 characters (the "serial number", often (ab)used as a password for banking and other purposes). While the dashes certainly don't need to be stored, they are not in arbitrary positions as they delimit the parts of the SSN noted above. One might even want to store the SSN in three columns, the first linked to a table of valid area numbers, the second error checked so "00" is not valid and so on or get even more fancy and error check against: http://www.ssa.gov/employer/highgroup.txt. It all depends on one's specific requirements. Google and you will find SSN info pages such as: http://proagency.tripod.com/usasssearch.html http://www.ssa.gov/foia/stateweb.html Cheers, Steve ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] returning a recordset from PLpg/SQL
Tom Lane wrote:
Stephan Szabo <[EMAIL PROTECTED]> writes:
On Tue, 2 Mar 2004, Terence Kearns wrote:
Well I haven't yet done anything because I couldn't get anything to
compile which returned SETOF RECORD..
As a starting point, SETOF "RECORD" is different from SETOF RECORD given
PostgreSQL's fold case to lower case for unquoted names.
Also, you can hardly expect a function to return a rowtype that doesn't
even exist until the function executes --- how the heck is the parser
supposed to make sense of the calling query? So the "execute create
type" part of this is nonsense, I'm afraid. The SETOF RECORD mechanism
will let you return a rowtype that is not known fully at the time the
function is written, but the rowtype does have to be known when the
calling query is parsed.
You might be able to replace the CREATE TYPE with an anonymous record
type in the calling query:
select ...
from details_for_profile(...) as x(doc_id int4,
doc_title varchar(256),
...);
A small improvement is to do a two-step process. From your app, you
first SELECT a function call that returns an SQL statement as a text
string, specific to att_data_type. Then you execute that as a second
step. For example:
--8<--
create table hdr(hid int primary key, context text);
insert into hdr values(1, 'test1');
insert into hdr values(2, 'test2');
create table att(aid int primary key, attname text, atttype text);
insert into att values (1, 'test_date', 'timestamp');
insert into att values (2, 'height', 'float8');
insert into att values (3, 'width', 'float8');
insert into att values (4, 'color', 'text');
create table det(
did int primary key,
hid int references hdr,
aid int references att,
val text
);
insert into det values(1,1,1,'15-mar-2004');
insert into det values(2,1,2,'3.14159');
insert into det values(3,1,3,'2.8');
insert into det values(4,1,4,'blue');
insert into det values(5,2,1,'16-mar-2004');
insert into det values(6,2,2,'2.34');
insert into det values(7,2,3,'3.28');
insert into det values(8,2,4,'red');
create or replace function exec_sql(int) returns setof record as '
DECLARE
lookup_row record;
v_atttype text := ;
rec record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_atttype := lookup_row.atttype;
END LOOP;
FOR rec IN execute ''SELECT h.hid, h.context, d.val::'' || v_atttype ||
'' FROM hdr h, att a, det d '' ||
'' WHERE d.hid = h.hid and d.aid = a.aid and a.aid = '' || $1
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' language plpgsql;
create or replace function write_sql(int) returns text as '
DECLARE
v_attname text := ;
v_atttype text := ;
v_result text;
lookup_row record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_attname := lookup_row.attname;
v_atttype := lookup_row.atttype;
END LOOP;
v_result := ''select hid, context, '' || v_attname ||
'' from exec_sql('' || $1 || '') as t(hid int, context
text, '' ||
v_attname || '' '' || v_atttype || '')'';
return v_result;
END;
' language plpgsql;
regression=# select write_sql(1);
write_sql
--
select hid, context, test_date from exec_sql(1) as t(hid int, context
text, test_date timestamp)
(1 row)
regression=# select hid, context, test_date from exec_sql(1) as t(hid
int, context text, test_date timestamp);
hid | context | test_date
-+-+-
1 | test1 | 2004-03-15 00:00:00
2 | test2 | 2004-03-16 00:00:00
(2 rows)
regression=# select write_sql(2);
write_sql
-
select hid, context, height from exec_sql(2) as t(hid int, context
text, height float8)
(1 row)
regression=# select hid, context, height from exec_sql(2) as t(hid int,
context text, height float8);
hid | context | height
-+-+-
1 | test1 | 3.14159
2 | test2 |2.34
(2 rows)
regression=# select write_sql(3);
write_sql
---
select hid, context, width from exec_sql(3) as t(hid int, context
text, width float8)
(1 row)
regression=# select hid, context, width from exec_sql(3) as t(hid int,
context text, width float8);
hid | context | width
-+-+---
1 | test1 | 2.8
2 | test2 | 3.28
(2 rows)
regression=# select write_sql(4);
write_sql
-
select hid, context, color from exec_sql(4) as t(hid int, context
text, color text)
(1 row)
regress
[SQL] SYSDATE in PostgreSQL !?
How can I define a table with columns with sysdate as the default value.. If there is no SYSDATE defined in PostgreSQL , what can I do ? Any help is appreciated. CREATE TABLE channels( channelID NUMBER PRIMARY KEY, name VARCHAR2(64) NOT NULL, statusINTEGER NOT NULL, remedyID VARCHAR2(15) UNIQUE, remedySchemaID NUMBER default 48, remedyLastModifiedINTEGER, updateTimeStamp DATE default (SYSDATE), createTimeStamp DATE default (SYSDATE) ); Regards, Louie Kwan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] User defined types -- Social Security number...
On Mon, Mar 01, 2004 at 09:42:48AM -0800, Steve Crawford wrote: > I missed the start of this thread but will chime in with a comment > anyway. > > My rule is to select an appropriate numeric type of data if you will > be doing numeric types of things to it, character types if you will > be doing character manipulations, etc. > > I don't know of any good reasons to need to know SSN/6.9, sqrt(SSN), > SSN+7.86 but there are plenty of good reasons to need the first three > characters (the "area number"), the middle two characters (the "group > number", and the last 4 characters (the "serial number", often > (ab)used as a password for banking and other purposes). Another excellent point. I often store zip codes as text for this reason. The only other thing that I would mention is that if the SSN field in the db will be a key of some sort, which is often the case, then it might be more efficient to store it as an integer. It might be more efficient to store it as a character string. The author should test in this case to determine the most efficient way. As for character vs. integer manipulations, in most scripting style languages, which is pretty much exlusively what I use, there's no need to think about types, and something like an SSN will silently change between being character or integer depending on what operations are being performed on it. Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] select by groups
I have a table with names of people, email address, etc, and an identifier that tells me which group they are in (could be a 1, 2, or 3 person group). Is it posible to make a query that would give me the name of the persons of each group in one row? Or do I have to do PL? -- 19:15:01 up 97 days, 1:24, 4 users, load average: 0.00, 0.07, 0.17 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] select by groups
Martin Marques <[EMAIL PROTECTED]> writes: > I have a table with names of people, email address, etc, and an identifier > that tells me which group they are in (could be a 1, 2, or 3 person group). > Is it posible to make a query that would give me the name of the persons of > each group in one row? Or do I have to do PL? Use GROUP BY with an aggregator that concatenate the names. The aggregator may be implemented in plpgsql, for instance: CREATE OR REPLACE FUNCTION concat (text, text) RETURNS text AS 'select case when $1 = then $2 else ($1 || '', '' || $2) end' LANGUAGE sql; CREATE AGGREGATE concat ( BASETYPE = text, SFUNC = public.concat, STYPE = text, INITCOND = '' ); Regards, Manuel. ---(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] SYSDATE in PostgreSQL !?
On Mon, 2004-03-01 at 21:18, Louie Kwan wrote: > How can I define a table with columns with sysdate as the default value.. > > If there is no SYSDATE defined in PostgreSQL , what can I do ? > CREATE TABLE channels( ... > updateTimeStamp DATE default (SYSDATE), > createTimeStamp DATE default (SYSDATE) > ); updateTimeStamp DATE DEFAULT CURRENT_DATE or possibly updateTimeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP which will give you time as well as date. Perhaps you should also add NOT NULL. CURRENT_* doesn't change within a transaction. If you need to record real time, use timeofday(). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] returning a recordset from PLpg/SQL
Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> On Tue, 2 Mar 2004, Terence Kearns wrote: >> >>> Well I haven't yet done anything because I couldn't get anything to >>> compile which returned SETOF RECORD.. > > > >> As a starting point, SETOF "RECORD" is different from SETOF RECORD given >> PostgreSQL's fold case to lower case for unquoted names. > > > > Also, you can hardly expect a function to return a rowtype that doesn't > even exist until the function executes --- how the heck is the parser > supposed to make sense of the calling query? So the "execute create > type" part of this is nonsense, I'm afraid. Right you are. I did mention that I didn't expect that code to work at all, I just used it as an indicator or a desired outcome. I'm sorry if I didn't make that clear enough. I just hoped that it would illustrate what I'm trying to achieve. And that is: "return a set of rows where the columns in that row are not yet determined." > The SETOF RECORD mechanism > will let you return a rowtype that is not known fully at the time the > function is written, but the rowtype does have to be known when the > calling query is parsed. Interesting. > > You might be able to replace the CREATE TYPE with an anonymous record > type in the calling query: > > select ... > from details_for_profile(...) as x(doc_id int4, >doc_title varchar(256), >...); > > regards, tom lane > yeah but then you're back to square one with dynamically building the columns to match the anonymous type you declared in the query. I've got some ideas about creating persistent types using RULEs on the attribute_profiles table. So when someone INSERTs or UPDATEs an attribute profile, a datatype going by the name 'profile_type_' || att_profile_id::text is created. That way the types are already known and maybe can somehow be passed to the details_for_profile() prcedure. I'll have to experiment and get back. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] returning a recordset from PLpg/SQL
I've read through all the example code you've provided thoroughly and
there are definately some useful ideas there. I changed the design of
the document_attribute_values table to only have one field, a text
field, to store the value. As your examples demosntrates, I can simply
cast the text value to whatever the entity_attribute record [for that
value] specifies.
I also think a 2-step approach is the way to go.
Thanks.
Joe Conway wrote:
Tom Lane wrote:
Stephan Szabo <[EMAIL PROTECTED]> writes:
On Tue, 2 Mar 2004, Terence Kearns wrote:
Well I haven't yet done anything because I couldn't get anything to
compile which returned SETOF RECORD..
As a starting point, SETOF "RECORD" is different from SETOF RECORD given
PostgreSQL's fold case to lower case for unquoted names.
Also, you can hardly expect a function to return a rowtype that doesn't
even exist until the function executes --- how the heck is the parser
supposed to make sense of the calling query? So the "execute create
type" part of this is nonsense, I'm afraid. The SETOF RECORD mechanism
will let you return a rowtype that is not known fully at the time the
function is written, but the rowtype does have to be known when the
calling query is parsed.
You might be able to replace the CREATE TYPE with an anonymous record
type in the calling query:
select ...
from details_for_profile(...) as x(doc_id int4,
doc_title varchar(256),
...);
A small improvement is to do a two-step process. From your app, you
first SELECT a function call that returns an SQL statement as a text
string, specific to att_data_type. Then you execute that as a second
step. For example:
--8<--
create table hdr(hid int primary key, context text);
insert into hdr values(1, 'test1');
insert into hdr values(2, 'test2');
create table att(aid int primary key, attname text, atttype text);
insert into att values (1, 'test_date', 'timestamp');
insert into att values (2, 'height', 'float8');
insert into att values (3, 'width', 'float8');
insert into att values (4, 'color', 'text');
create table det(
did int primary key,
hid int references hdr,
aid int references att,
val text
);
insert into det values(1,1,1,'15-mar-2004');
insert into det values(2,1,2,'3.14159');
insert into det values(3,1,3,'2.8');
insert into det values(4,1,4,'blue');
insert into det values(5,2,1,'16-mar-2004');
insert into det values(6,2,2,'2.34');
insert into det values(7,2,3,'3.28');
insert into det values(8,2,4,'red');
create or replace function exec_sql(int) returns setof record as '
DECLARE
lookup_row record;
v_atttype text := ;
rec record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_atttype := lookup_row.atttype;
END LOOP;
FOR rec IN execute ''SELECT h.hid, h.context, d.val::'' || v_atttype ||
'' FROM hdr h, att a, det d '' ||
'' WHERE d.hid = h.hid and d.aid = a.aid and a.aid = '' || $1
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' language plpgsql;
create or replace function write_sql(int) returns text as '
DECLARE
v_attname text := ;
v_atttype text := ;
v_result text;
lookup_row record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_attname := lookup_row.attname;
v_atttype := lookup_row.atttype;
END LOOP;
v_result := ''select hid, context, '' || v_attname ||
'' from exec_sql('' || $1 || '') as t(hid int, context
text, '' ||
v_attname || '' '' || v_atttype || '')'';
return v_result;
END;
' language plpgsql;
regression=# select write_sql(1);
write_sql
--
select hid, context, test_date from exec_sql(1) as t(hid int, context
text, test_date timestamp)
(1 row)
regression=# select hid, context, test_date from exec_sql(1) as t(hid
int, context text, test_date timestamp);
hid | context | test_date
-+-+-
1 | test1 | 2004-03-15 00:00:00
2 | test2 | 2004-03-16 00:00:00
(2 rows)
regression=# select write_sql(2);
write_sql
-
select hid, context, height from exec_sql(2) as t(hid int, context
text, height float8)
(1 row)
regression=# select hid, context, height from exec_sql(2) as t(hid int,
context text, height float8);
hid | context | height
-+-+-
1 | test1 | 3.14159
2 | test2 |2.34
(2 rows)
regression=# select write_sql(3);
write_sql
---
select hid, context, width from exec_sql(3) as t(hid int, context text,
width float8)
(1 row)
regression=# select hid, c
[SQL] scripts for converting postgres to oracle?
Hi there, We use oracle at work but I use postgres at home. I also sometimes develop something in postgres for use at work because I don't really want to run oracle (9i Lite won't install) on my laptop. At the moment, I'm hacking a nasty php script which converts a pgdump file which will work for a particular database. Does anyone have something pre-made which will *assist* with a postgres to oracle conversion? -- Terence Kearns ~ ph: +61 2 6201 5516 IT Database/Applications Developer Enterprise Information Systems Client Services Division University of Canberra www.canberra.edu.au ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] User defined types -- Social Security number...
On Sun, Feb 29, 2004 at 11:11:31PM -0500, Christopher Browne wrote: > The world rejoiced as [EMAIL PROTECTED] (Michael Chaney) wrote: > > Look, you're thinking way too hard on this. An SSN is a 9-digit number, > > nothing more. There are some 9-digit numbers which aren't valid SSN's, > > and you might want to get fancy and create a constraint for that. > > > > Regardless, you are making a *major* mistake of confusing data > > storage with rendering. It is common to *render* an SSN as > > xxx-xx- and its cousin the FETID (Federal Employers Tax ID) as > > xx-xxx. To store the dashes makes no sense. They're in the > > same place each time, it's wasted data. > > > > Store the SSN as an "integer". When you begin to think about this > > correctly, the "leading zeros" problem disappears since that is also a > > *rendering* issue. > > Well put. > > The one thing that is a bit unfortunate is that 32 bit ints aren't > quite big enough for this. You need 1 extra digit :-(. For what? The largest SSN is 999,999,999, a signed 32-bit int goes to just over 2,000,000,000. Ever hear of a "4GB limit"? Michael -- Michael Darrin Chaney [EMAIL PROTECTED] http://www.michaelchaney.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] What's wrong with my date/interval arithmetic?
Hi,
I ran this query on Postgres 7.3:
select min_time, max_time, min_time+age(max_time,min_time) as result,
to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS')
+age(to_timestamp('2003-12-01 03:50:45','-MM-dd HH24:MI:SS'),
to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS')) as expected_result
from
(select min(postingblogdate) as min_time from blg_weblog_header) as min_time,
(select max(postingblogdate) as max_time from blg_weblog_header) as max_time
and get a result like this:
min_time,max_time,result,expected_result
2003-10-17 23:07:00,2003-12-01 03:50:45,2003-12-02 03:50:45,2003-12-01 03:50:45
Why is the "result" incorrect (off by one day)?
When I do the exactly same arithmetic using timestamps created
with to_timestamp, everything is OK ("expected_result").
Is this a bug or am I doing something worng?
thanks for any thoughts about this problem,
Wojtek
P.S. postingblogdate is of type "timestamp without time zone"
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] What's wrong with my date/interval arithmetic?
Wojtek <[EMAIL PROTECTED]> writes:
> Why is the "result" incorrect (off by one day)?
> When I do the exactly same arithmetic using timestamps created
> with to_timestamp, everything is OK ("expected_result").
It is not "exactly the same arithmetic", because to_timestamp
delivers a result of type timestamp-with-time-zone, whereas your
other values are evidently timestamp without time zone. You did
not say what timezone setting you are using, but I think the
discrepancy is probably explained by that.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] What's wrong with my date/interval arithmetic?
TL> It is not "exactly the same arithmetic", because to_timestamp
TL> delivers a result of type timestamp-with-time-zone, whereas your
TL> other values are evidently timestamp without time zone. You did
TL> not say what timezone setting you are using, but I think the
TL> discrepancy is probably explained by that.
I tried doing this: (casting to 'timestamp without timezone')
select cast(to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS') as timestamp)
+age(cast(to_timestamp('2003-12-01 03:50:45','-MM-dd HH24:MI:SS') as
timestamp),
cast(to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS') as
timestamp))
And it turns out you were right Tom, the result is
2003-12-02 03:50:45, so the data type _does_ matter.
Investigating that a little further I found out that there is a difference
in results returned by age:
select age(cast(to_timestamp('2003-12-01 03:50:45','-MM-dd HH24:MI:SS') as
timestamp),
cast(to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS') as
timestamp))
is '1 mon 14 days 04:43:45'
and
select age(to_timestamp('2003-12-01 03:50:45','-MM-dd HH24:MI:SS'),
to_timestamp('2003-10-17 23:07:00','-MM-dd HH24:MI:SS'))
is '1 mon 13 days 04:43:45'
But it still doesn't answer the questions why there is a difference in
results and why it is exactly one day.
The "timestamp without time zone" arithmetic should produce correct
results when all operations are done _within_ that data type (without
mixing types), yet I'm getting this one day discrepancy.
puzzled Wojtek
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
