[SQL] returning a recordset from PLpg/SQL

[Terence Kearns]

Looking at the datatypes you can RETURN, it doesn't seem that there is a 
way to return a recordset

I tried
RETURNS SETOF RECORD
but that doesn't work
I even tried
RETURNS SETOF fooTable%ROWTYPE
What I would like to do is not that simple, I need to be able to 
build/declare a record definition with an arbitary list of columns and 
then return a set of that record which has it's values populated by 
queries and calculations inside the procedure.

returning an array is not a solution because I need to name all the 
columns and they will have different data types.



---(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] returning a recordset from PLpg/SQL

[Tom Lane]

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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Triggers

[Philip J. Boonzaaier]

The technical reference gives an example of a trigger on a table - employee
Just to test this, I have created the following table,
CREATE TABLE employee
(name VARCHAR(30),
age int4,
state VARCHAR(2),
manager VARCHAR(3),
adult VARCHAR(3)); 

The I created a simple Function, as follows :

CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS '
BEGIN
IF new.age > 20 THEN
new.adult = ''yes'';
ELSE
new.adult = ''no'';
END IF;
END;
' LANGUAGE 'plpgsql';

Finally, I defined the Trigger as :

CREATE TRIGGER employee_insert_update
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW EXECUTE PROCEDURE trig_insert_update_check_emp();

Now, when I execute the following :

INSERT INTO employee (name,age,state,manager)
VALUES ('sean',29,'tx','yes');

I get :

ERROR fmgr_info function 6264440 cache lookup failed


What am I doing wrong 



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] User defined types -- Social Security number...

[Greg Patnude]

Thanks Josh -- I understand that there are valid and invalid SSN's -- 
similar rules apply to zip codes and area codes...

I tried this:

SELECT to_char(123456789, '000-00-');
which yields 123-45-6789 -- nicely, I might add...

the trick is getting postgreSQL to do this without having to create an ON
SELECT and ON UPDATE TRIGGER...

an ON UPDATE, SELECT, and INSERT re-write RULE might do the trick...

SSN's CAN in fact start with a leading 0 -- mine does -- "041-xx-" --

I do agree that there are valid ranges -- my main concern is being able to
store any leading zeros - I just need to make sure that something "looks"
like a valid SSN in the formattig
(nnn-nn-) and that I can store / retrieve it with the approoriate
format -- what I am really trying to accomplish is an "input mask"...

I hadn't considered using a Domain have to look at that

-- 
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762

"Josh Berkus" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Greg,
>
> > Anyone have a good pre-built user-defined type definition for creating /
> > maintaining / manipulating a SSN ... where valid chars are in the range
> > 000-00- through 999-99-.
>
> Actually, the range is more narrowly defined than that.  I'm not sure of
the
> exact rules, but you will never see a leading 0 or a -00- in an SSN.
>
> > I imagine that the storage column is probably varchar(11) -- I am
looking
> > for a type definition that
>
> Use DOMAINs, not a custom type.  It's less work.
>
> > Either that or the question is: How can I coerce postgreSQL into using
an
> > input / output "mask"...
>
> After you've created your DOMAIN, based on the TEXT type, you can overload
the
> input and output functions to format correctly.   Beware, though: input &
> output functions pretty much have to be written in C.
>
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] plpgsql function returning SETOF RECORD Question

[jbduffy]

Hi

When I run the function below I recieve an error message saying that column 
definitions need to be specified for a function returing a type RECORD.

I was under the impression that the FOR row IN SELECT... loop would assign a column 
structure to the RECORD type. Am I wrong about this?

CREATE FUNCTION test() RETURNS SETOF RECORD AS '
DECLARE
  row  RECORD;
BEGIN
  FOR row IN SELECT * FROM dates LOOP
RETURN NEXT row;
  END LOOP;

  RETURN;
END;
' LANGUAGE 'plpgsql';

PostgreSQL 7.3.2-3 on Red Hat 9.

Regards

John Duffy

-
Email provided by http://www.ntlhome.com/



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Execute Query in Postgres Function

[Sumita Biswas (sbiswas)]

How do we execute a Query String in Postgresql function?
Or what is the equivalent of the following Code in MSSQL Stored
Procedure:


SET @QueryToPopulateTemp = "INSERT INTO
#Tmp_ConferenceDetail(ConferenceType, ConnectTime,
DisconnectTime,DestConversationId, Duration, AppConfId, OrigDeviceName,
DestDeviceName, destLegIdentifier) " + 
"SELECT  * FROM Tbl_Billing_Data ";


EXEC sp_executesql @QueryToPopulateTemp;


Thanks for any inputs.

Regards,
Sumita


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] VIEW on lookup table

[JJ Gabor]

Hello all,

I have a lookup table consisting of 100,000+ rows.

99% of the lookup values resolve to 'Unknown'.

Building the lookup table takes a long time.

I would like to remove the 'Unknown' entries from the
table and provide a VIEW to emulate them.

The VIEW would need to provide all 100,000+ rows by 
using the reduced lookup data and generating the 
remaining values on the fly.

The lookup table structure:

CREATE TABLE lookup_data (

id1 INTEGER,
id2 INTEGER,
name TEXT,

PRIMARY KEY (id1, id2)
);

id1 is an INTEGER; from 0 through to 50,000+
id2 is an INTEGER; either 9 or 16.

Example data:

INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a');
INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b');
INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c');
INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd');
INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e');
INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f');
INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g');
INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h');
INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i');
INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j');
INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k');
INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l');
..

In the example data, entries where id1 is 5,6,7,9 are 'Unknown';

The VIEW would return:

id1, id2, name
1,   9,   'a'
1,   16,  'b'
2,   9,   'c'
2,   16,  'd'
3,   9,   'e'
3,   16,  'f'
4,   9,   'g'
4,   16,  'h'
5,   9,   'Unknown'
5,   16,  'Unknown'
6,   9,   'Unknown'
6,   16,  'Unknown'
7,   9,   'Unknown'
7,   16,  'Unknown'
8,   9,   'i'
8,   16,  'j'
9,   9,   'Unknown'
9,   16,  'Unknown'
10,  9,   'k'
10,  16,  'l'

I am using Postgres 7.2.1, which prevents me using a 
function to return a result set.

Can I achieve this in pure SQL?

Many thanks,
JJ Gabor.










---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Field list from table

[Jan Pips]

How can I, using SELECT, get the full list of fields from a given table?

Pips



---(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] unicode(utf-8) problem !

[john]

the chinese utf-8 code haves  3 bytes
 
When I use the query command Like   "  
select * from phpbb_users where 
username='阿至' "
 
the Result is Too Much Records! :-(
user_id=9522, username= 
阿貝user_id=8261, username= 阿超user_id=6364, username= 
阿聰user_id=6141, username= 阿菲user_id=5490, username= 
阿苹user_id=4694, username= 阿蠻user_id=2618, username= 
阿虎user_id=1979, username= 阿至
 

[SQL] Unicode Support

[Sumita Biswas (sbiswas)]

Hi All,

This is regarding Unicode Encoding(UTF8) in Postgres.
I have the following assumptions, please correct me if I am wrong:

1. Postgres Supports unicode encoding(UTF8).
2. Any database takes the default encoding unless specified. Hence we
can configure a Database to support unicode(UTF8) while creating the
database.
3. The datatype which will store unicode data will be "character
varying" as there are no other datatypes like NVARCHAR(in MSSQL) in
Postgres. This will happen only when the Database is configured to
support unicode(UTF8) encoding.

Regards,
Sumita


---(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] CURSORS - Please help urgent.

[Sumita Biswas (sbiswas)]

The follow code gives me Error:

DECLARE CURSOR_GET_ALL_CONFERENCE_RECORDS CURSOR FOR Select
ConferenceType,CallManagerId,ClusterId,DestConversationId,AppConfId,Fina
lDestination FROM Tmp_Conference;

OPEN CURSOR_GET_ALL_CONFERENCE_RECORDS;

FETCH FROM CURSOR_GET_ALL_CONFERENCE_RECORDS INTO lv_ConfId,
li_CallManagerId, lv_ClusterId, li_DestConversationId, lv_AppConfId,
lv_FinalDestination;

-- Close and de-allocate cursor
CLOSE CURSOR_GET_ALL_CONFERENCE_RECORDS;

Error is as follows:
plpgsql: ERROR during compile of proc_conferencesummary near line 212
ERROR:  syntax error at or near "OPEN"

Please comment what can be wrong.

Regards,
Sumita

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 23, 2004 8:59 AM
To: Sumita Biswas
Cc: [EMAIL PROTECTED]
Subject: Re: Postgres DB 


"Sumita Biswas" <[EMAIL PROTECTED]> writes:
> But when I type the command "psql" and don't specify a DB name, it 
> says
> that:
> psql: FATAL:  Database "postgres" does not exist in the system
catalog.

Yes, because psql's default behavior is to try to connect to the DB with
the same name as your user name.  That doesn't mean that such a DB is
guaranteed to exist, or even that anything is going to try to create it
for you.  It's just a default behavior that people have found handy.

regards, tom lane


---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] PLSQL Question regarding multiple inserts

[Humble Geek]

Hi all. Quick and perhaps silly question, but...

I am using Pg 7.3. I am writing a function using pgplsql. This function will
perform multiple inserts. Let's say two of the inserts are as follows:

-- id is primary key
insert into users (id, username) values (nextval('someSeq'),'somename');

-- id is also a PK
insert into log (id, uid, message) values (nextval('someOtherSeq'),XXX,'New
Account');

Assume XXX is the id from the first insert. How do I get that number? Not
currval('someSeq') -  'cause someone else may have performed an insert - but
the id for that specific insert.

Thanks,

HG



---(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] Trouble with composite types

[Yasir Malik]

Hello,
I don't know why this message is not going through!

I have the following types:
create type street_type as
(
street_number   smallint,
street_name text,
apt_number  smallint
);

create type address_type as
(
street  street_type,
citytext,
state   char(2),
zip_codechar(5)
);

When I enter that into the command prompt, I the following message:
ERROR:  Attribute "street" has composite type street_type

Why is it giving me error message for something I know is true?  Also, how
do I add a member function to a type?
Thanks,
Yasir

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Field list from table

[rixder]

On Thu, Feb 26, 2004 at 10:51:41AM +0100, Jan Pips wrote:
> How can I, using SELECT, get the full list of fields from a given table?
> 
> Pips
> 
> 
Hi !

Try it:

select * from table limit 0;

rixder

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Trouble with composite types

[Joe Conway]

Yasir Malik wrote:
[attempts to use a composite type as a field data type of another
 composite type]
When I enter that into the command prompt, I the following message:
ERROR:  Attribute "street" has composite type street_type
Why is it giving me error message for something I know is true?  Also, how
do I add a member function to a type?
What you are trying to do is currently unsupported. There have been 
multiple discussions in the past on this -- try searching the archives 
for more info.

Joe

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] plpgsql function returning SETOF RECORD Question

[Joe Conway]

[EMAIL PROTECTED] wrote:
When I run the function below I recieve an error message saying that
column definitions need to be specified for a function returing a
type RECORD.
I was under the impression that the FOR row IN SELECT... loop would
assign a column structure to the RECORD type. Am I wrong about this?
CREATE FUNCTION test() RETURNS SETOF RECORD AS ' DECLARE row  RECORD;
 BEGIN FOR row IN SELECT * FROM dates LOOP RETURN NEXT row; END LOOP;
RETURN; END; ' LANGUAGE 'plpgsql';
Please look at the docs:
  http://www.postgresql.org/docs/current/static/sql-select.html
Specifically, the latter half of this paragraph:
  "Function calls can appear in the FROM clause. (This is especially
  useful for functions that return result sets, but any function can be
  used.) This acts as though its output were created as a temporary
  table for the duration of this single SELECT command. An alias may
  also be used. If an alias is written, a column alias list can also be
  written to provide substitute names for one or more attributes of the
  function's composite return type. If the function has been defined as
  returning the record data type, then an alias or the key word AS must
  be present, followed by a column definition list in the form
  ( column_name data_type [, ... ] ). The column definition list must
  match the actual number and types of columns returned by the
  function."
and the example further down the page:
  CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS '
   SELECT * FROM distributors WHERE did = $1;
  ' LANGUAGE SQL;
  SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
   f1  | f2
  -+-
   111 | Walt Disney
HTH,

Joe

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] How to get Rows Count

[Abdul Wahab Dahalan]

a   b  dc  
01  02 b*  
01  02 a*  
02  03   
02  04 b*  
02  04 a*  
03  05  
04  06  

If I've a table like above, how do I make a query to get rows count that 
doesnt have '*' in it.

Can we have other queries than this : Select count(c) from tablename 
where c !='*';
or Select count(c) from tablename where c <> '*';



---(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] Postgres DB

[Michael Chaney]

On Mon, Feb 23, 2004 at 02:24:32PM +0530, Sumita Biswas wrote:
> The following Query string , when executed does not give an Error, even
> though the table Tbl_Billing_Data is not present.
> 
> QueryToPopulateTemp := ''INSERT INTO Tmp_Conference(ConferenceType,
> CallManagerId, ClusterId, DestConversationId, ConnectDate,
> FinalDestination)''+'' SELECT 2, Global_CallId_CallManagerId,
> globalCallId_ClusterID, DestConversationID, StartDate = MIN
> (Connect_Date), Final_Destination ''+'' FROM Tbl_Billing_Data'';

I had a situation where a subquery had an error in it, and the query
simply returned no rows instead of throwing an error.

It looks like it might well be a bug that we're experiencing, I just
haven't had time to chase it down.

Michael
-- 
Michael Darrin Chaney
[EMAIL PROTECTED]
http://www.michaelchaney.com/

---(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] User defined types -- Social Security number...

[Michael Chaney]

On Sun, Feb 22, 2004 at 04:45:51PM -0800, Greg Patnude wrote:
> Thanks Josh -- I understand that there are valid and invalid SSN's --
> similar rules apply to zip codes and area codes...
>
> I tried this:
>
> SELECT to_char(123456789, '000-00-');
> which yields 123-45-6789 -- nicely, I might add...
>
> the trick is getting postgreSQL to do this without having to create an
> ON
> SELECT and ON UPDATE TRIGGER...
>
> an ON UPDATE, SELECT, and INSERT re-write RULE might do the trick...
>
> SSN's CAN in fact start with a leading 0 -- mine does -- "041-xx-"
> --
>
> I do agree that there are valid ranges -- my main concern is being
> able to
> store any leading zeros - I just need to make sure that something
> "looks"
> like a valid SSN in the formattig
> (nnn-nn-) and that I can store / retrieve it with the approoriate
> format -- what I am really trying to accomplish is an "input mask"...

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.

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.

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

2004-02-29 21:14:27.030434-06

We store:

2453065.88503472

It's easier to use that as a basic format from which we can render it in
any way we wish.

The same applies to your SSN.

Michael

-- 
Michael Darrin Chaney
[EMAIL PROTECTED]
http://www.michaelchaney.com/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] PLSQL Question regarding multiple inserts

[V i s h a l Kashyap @ [Sai Hertz And Control Systems]]

Dear Humble Geek ,

-- id is primary key
insert into users (id, username) values (nextval('someSeq'),'somename');
-- id is also a PK
insert into log (id, uid, message) values (nextval('someOtherSeq'),XXX,'New
Account');
Assume XXX is the id from the first insert. How do I get that number? Not
currval('someSeq') -  'cause someone else may have performed an insert 
 

Nop,
If  the insert and later on
currval('someSeq') is called inside the same function

currval will only return the current value that has been effected by the 
first  insert  made.



--
Best Regards,
Vishal Kashyap
Director / Lead Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com
Jabber IM: [EMAIL PROTECTED]
ICQ :  264360076
Yahoo  IM: [EMAIL PROTECTED]
---
You yourself, as much as anybody in the entire
universe, deserve your love and affection.
- Buddha
---
pgsql=# select marital_status from vishals_life;
marital_status
--
Single not looking
1 Row(s) affected

   ___
  //\\\
 ( 0_0 )
o0o-o0o-
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] How to get Rows Count

[Iain]

howabout:

select sum(case when c =  '*' then 0 else 1 end) as count_not_star from
tablename

If you want to process all records but only count thouse without a * in c
then this will do the trick.

regards
iain
- Original Message - 
From: "Abdul Wahab Dahalan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, March 01, 2004 11:06 AM
Subject: [SQL] How to get Rows Count


> >
> >
> >a   b  dc
> >01  02 b*
> >01  02 a*
> >02  03
> >02  04 b*
> >02  04 a*
> >03  05
> >04  06
> >
> If I've a table like above, how do I make a query to get rows count that
> doesnt have '*' in it.
>
> Can we have other queries than this : Select count(c) from tablename
> where c !='*';
> or Select count(c) from tablename where c <> '*';
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Field list from table

[Denis]

To get the list.. use 

SELECT a.attname as "Columns"
FROM  pg_attribute a, pg_class c
WHERE c.relname = ''
  AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum

Thanx

Denis


- Original Message - 
From: "Jan Pips" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 26, 2004 3:21 PM
Subject: [SQL] Field list from table


> How can I, using SELECT, get the full list of fields from a given table?
> 
> Pips
> 
> 
> 
> ---(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 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...

[Christopher Browne]

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.
-- 
If this was helpful,  rate me
http://cbbrowne.com/info/spiritual.html
"I owe the government $3400 in  taxes.  So I sent them two hammers and
a toilet seat."  -- Michael McShane

---(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: [HACKERS] [SQL] Materialized View Summary

[Hans-Jürgen Schönig]

Richard Huxton wrote:
On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote:

I've written a summary of my findings on implementing and using
materialized views in PostgreSQL. I've already deployed eagerly updating
materialized views on several views in a production environment for a
company called RedWeek: http://redweek.com/. As a result, some queries
that were taking longer than 30 seconds to run now run in a fraction of a
millisecond.
You can view my summary at
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html


Interesting (and well written) summary. Even if not a "built in" feature, I'm 
sure that plenty of people will find this useful. Make sure it gets linked to 
from techdocs.

If you could identify candidate keys on a view, you could conceivably automate 
the process even more. That's got to be possible in some cases, but I'm not 
sure how difficult it is to do in all cases.



Are there any plans to rewrite that in C and add proper support for SQL 
commands? (e.g. "CREATE MATERIALIZED VIEW", "DROP VIEW", ...).

	Best regards,

		Hans

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---(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: [HACKERS] [SQL] Materialized View Summary

[Jonathan Gardner]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tuesday 24 February 2004 01:48 pm, Robert Treat wrote:
> On Tue, 2004-02-24 at 12:11, Richard Huxton wrote:
> > On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote:
> > > I've written a summary of my findings on implementing and using
> > > materialized views in PostgreSQL. I've already deployed eagerly
> > > updating materialized views on several views in a production
> > > environment for a company called RedWeek: http://redweek.com/. As a
> > > result, some queries that were taking longer than 30 seconds to run
> > > now run in a fraction of a millisecond.
> > >
> > > You can view my summary at
> > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.htm
> > >l
>
> have you done much concurrency testing on your snapshot views? I
> implemented a similar scheme in one of my databases but found problems
> when I had concurrent "refresh attempts".  I ended up serializing the
> calls view LOCKing, which was ok for my needs, but I thought potentially
> problematic in other cases.
>

I don't actually use snapshot views in production. I would imagine that if 
you had two seperate processes trying to update the views simultaneously, 
that would be a problem. All I can say is "don't do that". I think you'd 
want to lock the table before we go and start messing with it on that 
scale.

We are running into some deadlock issues and some other problems with eager 
mvs, but they are very rare and hard to reproduce. I think we are going to 
start locking the row before updating it and see if that solves it. We also 
just discovered the "debug_deadlock" feature.

I'll post my findings and summaries of the information I am getting here 
soon.

I'm interested in whatever you've been working on WRT materialized views. 
What cases do you think will be problematic? Do you have ideas on how to 
work around them? Are there issues that I'm not addressing but should be?

> > Interesting (and well written) summary. Even if not a "built in"
> > feature, I'm sure that plenty of people will find this useful. Make
> > sure it gets linked to from techdocs.
>
> Done. :-)
>

*blush*

> > If you could identify candidate keys on a view, you could conceivably
> > automate the process even more. That's got to be possible in some
> > cases, but I'm not sure how difficult it is to do in all cases.
>
> it seems somewhere between Joe Conways work work arrays and polymorphic
> functions in 7.4 this should be feasible.
>

I'll have to look at what he is doing in more detail.

- -- 
Jonathan M. Gardner
Web Developer, Amazon.com
[EMAIL PROTECTED] - (206) 266-2906
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAO837BFeYcclU5Q0RAhonAKDBY7Svz9/vxmerS+y/h2mLgV1ZZQCdFlnd
7aMPFvRx4O8qg+sJfWkaBh8=
=zdhL
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org