"Hu, Patricia" writes:
> I have the following function and view in my db:
> create or replace function ${catalogSchema}.fn_show_pg_stat_activity()
> returns setof pg_catalog.pg_stat_activity as $$ select * from
> pg_catalog.pg_stat_activity; $$ language sql volatile security definer;
> create
I have the following function and view in my db:
create or replace function ${catalogSchema}.fn_show_pg_stat_activity() returns
setof pg_catalog.pg_stat_activity as $$ select * from
pg_catalog.pg_stat_activity; $$ language sql volatile security definer;
create or replace view ${catalogSchema}
Hi:
On Mon, May 16, 2016 at 9:46 AM, Sachin Kotwal wrote:
>> You need to drop the view before recreating it. Then it works. If you
>> changed the access to the view with grants or revokes, you also neet to
>> recreate them. They are dropped with the view.
> Sorry to say but If we need to drop and
Hi,
>
> *
>> *
>> *While trying to drop a column by replacing view definition from view it
>> throws an error saying cannot drop column from view.*
>> *=*
>> postgres=# create or replace view vi1 as select
>> id , name from orgda
Hello
Please post your answers to the list.
You need to drop the view before recreating it. Then it works. If
you changed the access to the view with grants or revokes, you also
neet to recreate them. They are dropped with the view.
Sorry to say but If we need to drop and replace
On Mon, May 16, 2016 at 8:49 AM, Shrikant Bhende
wrote:
> While working on the view I came across an unusual behaviour of the view,
> PostgreSQL do not allows to drop a column from the view, whereas same
> pattern of Create and Replace view works while adding a column.
This is probably because yo
Hello
On 05/16/2016 08:49 AM, Shrikant Bhende wrote:
Hi all,
While working on the view I came across an unusual behaviour of the view,
PostgreSQL do not allows to drop a column from the view, whereas same
pattern of Create and Replace view works while adding a column.
Please find below test fo
Hi all,
While working on the view I came across an unusual behaviour of the view,
PostgreSQL do not allows to drop a column from the view, whereas same
pattern of Create and Replace view works while adding a column.
Please find below test for the same.
*Version info *
*===*
postgres=#
On 03/26/2016 12:47 PM, Boyan Botev wrote:
Thanks, Adrian! That worked great for what I needed. I greatly
appreciate your help. Do you know if there is a way to also display
system columns like xmin, xmax with this extension. I can see the need
for that in some future investigation.
You are not
Thanks, Adrian! That worked great for what I needed. I greatly appreciate
your help. Do you know if there is a way to also display system columns
like xmin, xmax with this extension. I can see the need for that in some
future investigation.
Thanks,
Boyan
On Sat, Mar 26, 2016 at 1:45 PM, Adrian Kla
That works only if I know I will need the deleted/updated records for a
table ahead of time. I need this feature/ability as a way to perform "data
forensics" on a random table that someone has done something to. Next time
it may be another table. Basically I need a way to remove the visibility
map
On 03/26/2016 10:32 AM, Boyan Botev wrote:
I want to view deleted records in table from a week ago in order to
troubleshoot a data issue. The table has not been vacuumed yet.
I was
Are you sure?
In other words do you have autovacuum turned off?
Autovacuum is on
On Sat, Mar 26, 2016 at 12:23 PM, Adrian Klaver
wrote:
> On 03/26/2016 09:08 AM, Boyan Botev wrote:
>
>> I want to view deleted records in table from a week ago in order to
>> troubleshoot a data issue. The table has not been vacuumed yet. I was
>>
>
> Are you sure?
> In other words do you have a
>
>
> I want to view deleted records in table from a week ago in order to
>> troubleshoot a data issue. The table has not been vacuumed yet. I was
>>
>
> Are you sure?
> In other words do you have autovacuum turned off?
Autovacuum is on. The table is fairly static and the last vacuum analyze
was
On 03/26/2016 09:08 AM, Boyan Botev wrote:
I want to view deleted records in table from a week ago in order to
troubleshoot a data issue. The table has not been vacuumed yet. I was
Are you sure?
In other words do you have autovacuum turned off?
trying to use the pageinspect v1.4 extension but
I want to view deleted records in table from a week ago in order to
troubleshoot a data issue. The table has not been vacuumed yet. I was
trying to use the pageinspect v1.4 extension but can't seem to convert the
t_data to a readable record. Is there an easy way for me to get a SQL to
produce all s
On 09/08/2015 06:44 AM, Melvin Davidson wrote:
>ERROR: permission denied for relation sub_item
So obviously, the user doing the select on the view (ie: YOU) does not
have SELECT permission on table sub_item.
It is more subtle then that:
test=# select current_user;
current_user
---
On 09/08/2015 05:19 AM, Ramesh T wrote:
HI ,
I have view .when i try to select view ,it had return
select * from art;
ERROR: permission denied for relation sub_item
Pretty self-explanatory, the user running the query does not have rights
on sub_item, which I presume is part of the
>ERROR: permission denied for relation sub_item
So obviously, the user doing the select on the view (ie: YOU) does not
have SELECT permission on table sub_item.
On Tue, Sep 8, 2015 at 8:19 AM, Ramesh T
wrote:
> HI ,
> I have view .when i try to select view ,it had return
> select * f
HI ,
I have view .when i try to select view ,it had return
select * from art;
ERROR: permission denied for relation sub_item
SQL state: 42501
I am partitioning a number of tables in our system and am using a view on the
real master table with 'instead of’ triggers to insert and update rows. I have
used a view because my application uses Hibernate which does not work well with
triggers that return a null to indicate no further process
Brian Hirt writes:
> None of the relations used by vcredit_info are views. Â They are all
> tables. Â Oddly, I dropped the view and recreated it and the problem went
> away. Â Earlier I was just using create or replace view and the problem
> persisted.
Hm. I think that CREATE OR REPLACE VIEW m
None of the relations used by vcredit_info are views. They are all tables.
Oddly, I dropped the view and recreated it and the problem went away. Earlier
I was just using create or replace view and the problem persisted. The schema
was created by using pg_restore from an 8.4 custom dump.
Brian Hirt writes:
> I'm upgrading our database from 8.4 to 9.2 and I've run across a view that is
> no longer working. When selecting from the view, I get a permission denied
> error on one of the referenced tables. However, I can run the view's query
> directly without problems and I have
I'm upgrading our database from 8.4 to 9.2 and I've run across a view that is
no longer working. When selecting from the view, I get a permission denied
error on one of the referenced tables. However, I can run the view's query
directly without problems and I have read access to all the tab
On Thu, Feb 21, 2013 at 9:38 AM, bricklen wrote:
> A while back I was looking for a way to display object privileges
> quickly with a bit better readibility. The following view is what I
> came up with. Suggestions and improvements welcome (or comments
> stating that there are much easi\er ways to
A while back I was looking for a way to display object privileges
quickly with a bit better readibility. The following view is what I
came up with. Suggestions and improvements welcome (or comments
stating that there are much easi\er ways to get the same details).
(was created in a utility "admin"
Philipp Kraus writes:
> Can I create a view, that can be used like a table, so on insert statements
> the default values are set?
Sure. Just use ALTER VIEW SET DEFAULT to attach default values to the
columns of the view.
regards, tom lane
--
Sent via pgsql-general ma
Hello,
I use some views for creating some different accessibility of the table data.
For inserting I use a rule, which works at the moment, but
my rules runs the SQL statement "insert into myschema.mytable values (new.*)"
If I run on my view the command: insert into myview (field1, field4) value
- Original Message -
From: Igor Romanchenko
>On Fri, Nov 9, 2012 at 9:21 PM, George Weaver wrote:
>>Hi Everyone,
>>I have a view made up of a local query unioned with a view comprised of a
>>dblink query.
>>If the dblink query cannot establish a connection, I get the "could not
>>
On Fri, Nov 9, 2012 at 9:21 PM, George Weaver wrote:
> **
> Hi Everyone,
>
> I have a view made up of a local query unioned with a view comprised of a
> dblink query.
>
> If the dblink query cannot establish a connection, I get the "could not
> connect to server" error and the whole view fails.
>
Hi Everyone,
I have a view made up of a local query unioned with a view comprised of a
dblink query.
If the dblink query cannot establish a connection, I get the "could not connect
to server" error and the whole view fails.
Setting fail_on_error to false does not work in this situation.
Is th
On 07/25/2012 08:19 PM, Chris Bartlett wrote:
Ah! The bob schema has no access privileges set. I had used pgAdmin3 -
the schema definition pgAdmin3 reports is:
CREATE SCHEMA bob AUTHORIZATION bob;
(i.e., no grants)
So:
GRANT ALL ON SCHEMA bob TO bob;
DROP VIEW bob.people;
CREATE OR REPLACE V
At 7:51 PM -0700 25/7/12, Adrian Klaver wrote:
On 07/25/2012 07:47 PM, Chris Bartlett wrote:
At 7:37 PM -0700 25/7/12, Adrian Klaver wrote:
I am guessing if you do show search_path; from psql you will see that
the public schema is before the bob schema. The SELECT for the
unqualified people ta
On 07/25/2012 07:47 PM, Chris Bartlett wrote:
At 7:37 PM -0700 25/7/12, Adrian Klaver wrote:
I am guessing if you do show search_path; from psql you will see that
the public schema is before the bob schema. The SELECT for the
unqualified people table in CREATE VIEW bob.people_view will find
pub
At 7:37 PM -0700 25/7/12, Adrian Klaver wrote:
I am guessing if you do show search_path; from psql you will see
that the public schema is before the bob schema. The SELECT for the
unqualified people table in CREATE VIEW bob.people_view will find
public.people first in that case.
I don't thin
On 07/25/2012 07:25 PM, Chris Bartlett wrote:
I am not sure if this is expected behaviour or a bug.
Using PG 9.2 beta 2 and PGAdmin3 1.16 beta 2.
Connect as bob (superuser)
In public schema:
create table people (cols...)
create view people_view as select * from people
Create schema bob
I am not sure if this is expected behaviour or a bug.
Using PG 9.2 beta 2 and PGAdmin3 1.16 beta 2.
Connect as bob (superuser)
In public schema:
create table people (cols...)
create view people_view as select * from people
Create schema bob
create table bob.people (cols...)
create view
Hi,
how about this one?
SELECT n.nspname, c.relname, a.attname
FROM pg_depend d
INNER JOIN pg_class c ON (c.oid = refobjid)
INNER JOIN pg_attribute a ON (c.oid = a.attrelid AND d.refobjsubid =
a.attnum)
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
INNER JOIN pg_rewrite rw ON
Hi all,
I would like to be able to determine which tables each field of a view
comes from.
I have a view definition like
SELECT tbl_a.fld_a, tbl_b.fld_b AS fld_e, function(c,d,f) as fld_c,
(SELECT fld_d FROM tbl_d WHERE tbl_d.fld_e=tbl_a.fld_a) as fld_d FROM
tbl_a inner join tbl_b on tbl_a.
Thanks Tom.
I get it. Putting it in another way, if there was a function and a VIEW and
this field name were to be changed, then we'd have a broken function
anyway.
The only issue is that (before writing this mail) I expected that a VIEW
would either throw up errors or would work without fail reg
Robins Tharakan writes:
> This is a case where I changed the name of a field in a table that a VIEW
> referred to, but the VIEW definition still points to the old name of the
> field. The surprise is that the VIEW still works (with live data).
Specifically, you mean that you had a column referenc
Hi,
This is a case where I changed the name of a field in a table that a VIEW
referred to, but the VIEW definition still points to the old name of the
field. The surprise is that the VIEW still works (with live data).
Excerpt from psql (v9.1.2) given below.
The problem came up when I took a sche
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Tuesday, August 23, 2011 5:51 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] View "Caching" - Is this Known and Expected Behavior?
"David Johnston" writes:
> I a
On Tue, Aug 23, 2011 at 4:36 PM, David Johnston wrote:
> Hey All,
>
> I am wondering whether the behavior I am observing is expected. The rough
> scenario I have setup goes as follows (I can likely put together a test
> script if that is warranted):
>
> version
>
> PostgreSQL 9.0.3 on x86_64-pc-l
"David Johnston" writes:
> I am wondering whether the behavior I am observing is expected.
No, it isn't. Please provide a concrete test case.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
h
Hey All,
I am wondering whether the behavior I am observing is expected. The rough
scenario I have setup goes as follows (I can likely put together a test
script if that is warranted):
version
PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5) 4.
Thanks for the thorough explanation and link to more docs. Very much
appreciated!
Scott
On Aug 24, 2009, at 11:03 AM, Raymond O'Donnell wrote:
On 24/08/2009 18:37, Scott Frankel wrote:
If I understand how tables are managed internally, there are 2
sequences: my explicit foo_id and the
On 24/08/2009 18:37, Scott Frankel wrote:
> If I understand how tables are managed internally, there are 2
> sequences: my explicit foo_id and the internal sequence
> foo_foo_id_seq:
>
> public | foo_foo_id_seq | sequence | pguser |
>
> It's this internal sequence that must be involved in the c
Got it! Yes, this started happening after loading from a pg_dump.
Thanks for the explanation!
Scott
On Aug 24, 2009, at 10:52 AM, Tom Lane wrote:
Scott Frankel writes:
Is there a command that lists the values for the internal,
foo_foo_id_seq, sequence?
select * from foo_foo_id_seq;
Scott Frankel writes:
> Is there a command that lists the values for the internal,
> foo_foo_id_seq, sequence?
select * from foo_foo_id_seq;
The usual way to get into this sort of trouble is to load a bunch of
data into the table while explicitly specifying ID values. It will
take the data (a
Hi Ray,
On Aug 24, 2009, at 9:48 AM, Raymond O'Donnell wrote:
On 24/08/2009 17:31, Scott Frankel wrote:
The insert that yields the error seems innocuous enough:
INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019,
2);
It seems as if there's a sequence (foo_pkey) that's got
On 24/08/2009 17:31, Scott Frankel wrote:
> The insert that yields the error seems innocuous enough:
>
> INSERT INTO foo (color_id, ordinal, person_id) VALUES (1, 1019, 2);
>
> It seems as if there's a sequence (foo_pkey) that's got some weird
> values in it. The table itself looks like this
On 24/08/2009 17:31, Scott Frankel wrote:
> Is it possible to select or otherwise view a table's primary key values?
[snip]
> CREATE TABLE foo (
> foo_idSERIALPRIMARY KEY,
select foo_id from foo;
?
or am I missing something?
Ray.
--
Hello,
Is it possible to select or otherwise view a table's primary key values?
I'm troubleshooting the following error:
ERROR: duplicate key value violates unique constraint "foo_pkey"
The insert that yields the error seems innocuous enough:
INSERT INTO foo (color_id, ordi
It is generally better to save the balance. The general rule in
accounting systems design is "what can be printed, should be
explicitly on disk". for an invoice:
value before tax, tax percentage, value after tax, total before tax,
total after tax, etc, should all be saved explicitly.
An account s
> How often are you using the "bank balance" value?
I have no data on this as of yet, but it obviously needs to be correct
for when the user looks at it.
> The opposite argument, is how long does the computation take?
The computation is simple, however the amount of data that goes into
it g
On Dec 15, 11:25 am, r...@iol.ie ("Raymond O'Donnell") wrote:
> On 15/12/2008 16:14, Ketema Harris wrote:
>
> > if i have a "column" that is a calculation, say a bank balance -> sum of
> > all the debits and credits...is it more efficient to make a view that
> > executes the underlying calc query d
> if i have a "column" that is a calculation, say a bank balance -> sum
> of all the debits and credits...is it more efficient to make a view
> that executes the underlying calc query doing the math, or to create a
> table that has a column called balance that is updated for each
> transaction?
>
On 15/12/2008 16:14, Ketema Harris wrote:
> if i have a "column" that is a calculation, say a bank balance -> sum of
> all the debits and credits...is it more efficient to make a view that
> executes the underlying calc query doing the math, or to create a table
> that has a column called balance t
if i have a "column" that is a calculation, say a bank balance -> sum
of all the debits and credits...is it more efficient to make a view
that executes the underlying calc query doing the math, or to create a
table that has a column called balance that is updated for each
transaction?
so
Vivek Khera <[EMAIL PROTECTED]> writes:
> On Jul 11, 2008, at 4:24 AM, Richard Huxton wrote:
>> If you just want to see if a lock has been taken (e.g. SELECT FOR
>> UPDATE) then that shows in pg_locks. If you want details on the
>> actual rows involved, then you probably want "pgrowlocks" menti
On Jul 11, 2008, at 4:24 AM, Richard Huxton wrote:
If you just want to see if a lock has been taken (e.g. SELECT FOR
UPDATE) then that shows in pg_locks. If you want details on the
actual rows involved, then you probably want "pgrowlocks" mentioned
in Appendix F. Additional Supplied Module
Michael Shulman wrote:
Hi,
This seems like it must be a common question, but Google can't find
the answer for me. How do I view the currently open row-level locks?
The manual says that the table pg_locks doesn't contain row-level
locks, since they are stored on disk rather than in memory, but i
Hi,
This seems like it must be a common question, but Google can't find
the answer for me. How do I view the currently open row-level locks?
The manual says that the table pg_locks doesn't contain row-level
locks, since they are stored on disk rather than in memory, but it
doesn't say how one can
Karen Springer <[EMAIL PROTECTED]> writes:
> I need employeeName to be a text field in Access. I have tried casting
> the fields in the view as varchar, but it seem to default back to ::text.
The result of a || operator is always going to be type text. Put the
cast around the whole expression,
Hi,
I have a table
CREATE TABLE "HR"."Participant_Names"
(
"PIP" int4 NOT NULL,
"LastNAME" varchar(32) NOT NULL,
"FirstName" varchar(20) NOT NULL,
"NameUsed" varchar(20),
CONSTRAINT "pkey_PIP" PRIMARY KEY ("PIP"),
)
WITHOUT OIDS;
and a view
CREATE OR REPLACE VIEW "HR"."tvw_EmployeeNames_A
Em Friday 16 November 2007 18:57:24 Ed L. escreveu:
>
> I often have need for views that reference views that reference
> views, and so on. When I need to make a small update to one of
> the views, I am faced with having to drop and recreate all
> dependent views even if the driving change just ad
Thanks, Justin.
On Friday 16 November 2007 4:38 pm, Justin Pasher wrote:
> We have a system that has quite a few views to access some of
> the data (although we purposely tried to avoid views that
> pulled from other view due to some performance issues), but
> when we had all of the view interdepe
Ed L. wrote:
On Friday 16 November 2007 2:48 pm, Scott Marlowe wrote:
On Nov 16, 2007 3:43 PM, Ed L. <[EMAIL PROTECTED]> wrote:
That looks about as ugly as can be. Ugh. What it appears
to boil down to is that views become unusable unless you are
willing to invest the effort in a compl
On Friday 16 November 2007 2:48 pm, Scott Marlowe wrote:
> On Nov 16, 2007 3:43 PM, Ed L. <[EMAIL PROTECTED]> wrote:
> > That looks about as ugly as can be. Ugh. What it appears
> > to boil down to is that views become unusable unless you are
> > willing to invest the effort in a complex build sy
On Fri, Nov 16, 2007 at 02:43:01PM -0700, Ed L. wrote:
> That looks about as ugly as can be. Ugh. What it appears to
> boil down to is that views become unusable unless you are
> willing to invest the effort in a complex build system. The DB
You're kidding, right? You don't think that a bui
On Nov 16, 2007 4:43 PM, Ed L. <[EMAIL PROTECTED]> wrote:
> On Friday 16 November 2007 2:09 pm, Merlin Moncure wrote:
> > you have to rig a build system. if you have a lot of views
> > (which is good), and keeping them up to date is a pain, you
> > have to automate their creation. simplest way to d
On Nov 16, 2007 3:43 PM, Ed L. <[EMAIL PROTECTED]> wrote:
>
> That looks about as ugly as can be. Ugh. What it appears to
> boil down to is that views become unusable unless you are
> willing to invest the effort in a complex build system. The DB
> should handle this issue automatically. Does O
On Friday 16 November 2007 2:09 pm, Merlin Moncure wrote:
> you have to rig a build system. if you have a lot of views
> (which is good), and keeping them up to date is a pain, you
> have to automate their creation. simplest way to do that is to
> rig a build system around sql scripts. when you c
On Nov 16, 2007 4:01 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
> On Fri, 16 Nov 2007 13:57:24 -0700
> "Ed L." <[EMAIL PROTECTED]> wrote:
>
> > I have a question about view management...
> >
> > I often have need for views that reference views that reference
> > views, and so on. When I need t
On Nov 16, 2007 4:02 PM, Ed L. <[EMAIL PROTECTED]> wrote:
> On Friday 16 November 2007 1:57 pm, Ed L. wrote:
> > I have a question about view management...
> >
> > I often have need for views that reference views that
> > reference views, and so on. When I need to make a small
> > update to one of
On Fri, Nov 16, 2007 at 02:02:37PM -0700, Ed L. wrote:
> subtree of view dependencies just to change one minor aspect of
> an independent view.
Well, it's not independent, if other things depend on it, is it?
:)
A
--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke
--
On Friday 16 November 2007 1:57 pm, Ed L. wrote:
> I have a question about view management...
>
> I often have need for views that reference views that
> reference views, and so on. When I need to make a small
> update to one of the views, I am faced with having to drop and
> recreate all dependen
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Fri, 16 Nov 2007 13:57:24 -0700
"Ed L." <[EMAIL PROTECTED]> wrote:
> I have a question about view management...
>
> I often have need for views that reference views that reference
> views, and so on. When I need to make a small update to one of
I have a question about view management...
I often have need for views that reference views that reference
views, and so on. When I need to make a small update to one of
the views, I am faced with having to drop and recreate all
dependent views even if the driving change just adds another
co
It seems that insertion triggers on views will/may be added in version 8.3.
http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php
However, Tom mentioned that adding update and deletion triggers may be a bad
idea:
http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php
...
Ho
am Tue, dem 19.12.2006, um 13:32:10 +0900 mailte Richard Ollier folgendes:
> Hello,
>
> I was wondering it there was a way to optimize a view.
> If my view contains 20 LEFT joins, my guessing is that each time I will
> make a select on it, the view will be completely recalculated. Is there
> a
Hello,
I was wondering it there was a way to optimize a view.
If my view contains 20 LEFT joins, my guessing is that each time I will
make a select on it, the view will be completely recalculated. Is there
a way to have it up and running all the time like a table would be ?
Any advice here is
Richard Huxton wrote:
Larry White wrote:
SELECT * FROM Tasks
UNION ALL
SELECT * FROM Issues;
In case you care about where a record originated from:
SELECT *, 'Tasks' AS source FROM Tasks
UNION ALL
SELECT *, 'Issues' AS source FROM Issues;
Regards,
--
Alban Hertroys
[EMAIL PROTECTED]
magprod
In addition to the other replies (regarding UNION), are you aware that
you can use inheritance in postgres to factor out common fields ? Then
you could use the parent table to access the common denominator of the
children. I'm not sure though if this fits your needs, just worth
mentioning.
See also
Larry White wrote:
I need a read only view that concatenates data from two tables. Basically
both tables would need a simple query like
Select name, description, date from Tasks;
Select name, description, date from Issues;
Is there some way to wrap these two independent queries in a "CREATE VI
On Mar 10, 2006, at 0:55 , Larry White wrote:
Is there some way to wrap these two independent queries in a
"CREATE VIEW myview AS" statement?
CREATE VIEW myview AS
Select name, description, date from Tasks
UNION
Select name, description, date from Issues;
Michael Glaesemann
grzm myrealbox c
On Thu, Mar 09, 2006 at 10:55:12AM -0500, Larry White wrote:
> I need a read only view that concatenates data from two tables. Basically
> both tables would need a simple query like
>
> Select name, description, date from Tasks;
>
> Select name, description, date from Issues;
>
> Is there some w
I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like
Select name, description, date from Tasks;
Select name, description, date from Issues;
Is there some way to wrap these two independent queries in a "CREATE VIEW myview AS" statem
=?iso-8859-1?Q?Klein_Bal=E1zs?= <[EMAIL PROTECTED]> writes:
> But later in the same blog it seems to indicate that there is a choice to
> either use or not use the query cache of pg.
Hm? There is no query cache in PG.
regards, tom lane
---(end of
thanks for the help
>What you could do is partition the table so that critical information is
>stored in a smaller table while everything else goes to a larger table.
I was thinking the other way round - maybe I can split the large table by
creating a materialized view. But than I read that it is
On Wed, Dec 21, 2005 at 10:49:29PM +0100, Klein Bal?zs wrote:
> I thought that if I used a view to retrieve data its content might be cached
> so it would make the query faster.
No. A view is essentially exactly the same as inserting the view
definition into the query that's using it. IE:
CREATE
Klein Balázs wrote:
Hello everybody,
I have a table that stores responses to questions in different
questionnaires. This table will grow to millions of rows.
[snip]
I can store the fact that it is biodata in the bio field - it is biodata if
the value of that field is not 0 and I can index that
I thought that if I used a view to retrieve data its content might be cached
so it would make the query faster.
Am Mittwoch, 21. Dezember 2005 21:27 schrieb Klein Balázs:
> Or should I instead create a view that contains only the biodata and
select
> from that? But will postgres use the indexes th
Am Mittwoch, 21. Dezember 2005 21:27 schrieb Klein Balázs:
> Or should I instead create a view that contains only the biodata and select
> from that? But will postgres use the indexes than? Would that be a better
> approach?
Whether the query is executed by a view or typed in in its full form by h
Hello everybody,
I have a table that stores responses to questions in different
questionnaires. This table will grow to millions of rows.
My problem is that while most of the data in the table are rarely used in
queries one type of response will be used quite often: biodata - name,
gender, e-mail
Tom Lane wrote:
Rich Doughty <[EMAIL PROTECTED]> writes:
I have a view vw_tokens defined as
...
I cannot however perform a meaningful join against this view.
...
PG forms the full output of the view.
You seem to be wishing that PG would push the INNER JOIN down inside the
nested LEFT JOINs.
Rich Doughty <[EMAIL PROTECTED]> writes:
> I have a view vw_tokens defined as
> ...
> I cannot however perform a meaningful join against this view.
> ...
> PG forms the full output of the view.
You seem to be wishing that PG would push the INNER JOIN down inside the
nested LEFT JOINs. In general,
John McCawley wrote:
You should be able to use my trick...the join that is giving you the
problem is:
SELECT *
FROM
tokens.ta_tokenhist h INNER JOIN
tokens.vw_tokenst ON h.token_id = t.token_id
WHERE
h.sarreport_id = 9 ;
ta_tokenhist is already part of your view, right?
1 - 100 of 143 matches
Mail list logo
