Hi,
try DBVisualizer
--
Regards,
Bartek
Hi All
Let's assume I've got 3 tables:
- OrgStructure.tblUnits,
- OrgStructure.tblUnitStructure,
- Dictionary.tblUnits
I would like to do the EXPLAIN:
EXPLAIN
SELECT * FROM OrgStructure.tblUnits, OrgStructure.tblUnitStructure,
Dictionary.tblUnits
(Of course its cartesian product -
Works like a charm :)
thanks a lot.
Regards,
Bartek
2013/6/27 Pavel Stehule pavel.steh...@gmail.com
Hello
2013/6/27 Bartosz Dmytrak bdmyt...@gmail.com:
Hi All
Let's assume I've got 3 tables:
OrgStructure.tblUnits,
OrgStructure.tblUnitStructure,
Dictionary.tblUnits
I would
Hi all
is it possible to introduce similar solution for Windows systems in future?
I am aware it is not available because of lack of posix_fadvise function,
but I believe there is a way to introduce this feature for Win systems.
Regards,
Bartek
2013/1/30 Albe Laurenz laurenz.a...@wien.gv.at
The most likely explanation for what you observe is that
the functions have never been called since track_functions
has been set to all.
You can see if that is indeed the reason by calling one
of your invisible functions and see if it becomes
Hi all,
Does anyone have an idea why it works like this?
Regards,
Bartek
2013/1/29 Adrian Klaver adrian.kla...@gmail.com
Not quite sure what you are asking.
I am asking for info why not all functions are tracked.
All - I mean plpgsql functions. Just like I said before, I am aware not
all functions all tracked but my functions (written in plpgsql) should be.
2013/1/29 Adrian Klaver adrian.kla...@gmail.com
Are they never tracked or just sometimes?
Is it particular functions or random?
and this is strange for me.
I have few DBs with the same function (copy - paste), in one DB they are
tracked (visible in pg_stat_user_functions) in other not. In
Hi all,
I've notice not all my functions are tracked by pg_stat_user_functions view.
Interesting thing is similar functions in different db are tracked
correctly.
query:
SELECT p.* FROM pg_proc p
LEFT JOIN pg_stat_user_functions stat
ON (p.OID = stat.funcid)
INNER JOIN pg_language l
ON (l.oid =
2012/12/7 Tom Lane t...@sss.pgh.pa.us
Postmaster log messages are written in whatever the database_encoding
is, so if you've got multiple databases with different encodings, the
encoding in the log will be inconsistent.
Thanks for your answer Tom.
but...
all DBs are encoded in UTF8 (SELECT
Hi,
according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
DO returns void:
*The code block is treated as though it were the body of a function with
no parameters, returning void.*
*
*
Regars
Bartek
Pozdrawiam,
Bartek
2012/11/23 Peter Kroon plakr...@gmail.com
Hello,
I wish
Hi,
thanks, this will help me :)
Maybe one small hint:
You use only table name variable (p_table_name) which I assume should
contain schema name. If so then quote_ident ('aaA.bbbB') will give You
aaA.bbbB but not aaA.bbbB. This will produce error. It is better
idea, in my oppinion, to add
2012/7/26 Bruce Momjian br...@momjian.us
What is the pg_class table size limit then? Is that really helping
anyone?
Fist of all - thanks for Your attentions, I really appreciate it.
is that helping? - as it has been mentioned before: a small audience has
noticed that fact, so probably not.
2012/7/16 Philip Couling p...@pedal.me.uk
Is there any more flexible way to do this?
Hi,
in my opinion you should use fully qualified names instead of set
search_path
Your script should look like this:
CREATE OR REPLACE FUNCTION my_schema.foo()
RETURNS INTEGER AS
$BODY$
BEGIN
2012/7/13 Chris Angelico ros...@gmail.com
Does that help?
Sure :)
I know what unlimited means, but I suggest to change docs to be more
accurate.
Those limits are huge (e.g. number of indexes limited by pg_class table
size), but still exists.
it is like the famous Henry Ford's color choose:
2012/7/12 Craig Ringer ring...@ringerc.id.au
I suspect that's a pretty slow way to try to fill your DB up. You're
doing individual INSERTs and possibly in individual transactions (unsure, I
don't use PgAdmin); it's not going to be fast.
Try COPYing rows in using psql. I'd do it in batches
2012/7/12 David Johnston pol...@yahoo.com
How about saying: No Fixed Limit - see Table Size
I am sorry for delay. My intention was to start discussion about unlimited
number of rows.
I like this idea: No Fixed Limit - see Table Size
Another, maybe only academic, discussion is about maximum
Hi All
I found PG limitations (http://www.postgresql.org/about/):
- Maximum Rows per Table - Unlimited
- Maximum Table Size - 32 TB
My question is:
how is it possible to *reach* unlimited rows in table?
I did a test:
1. Create Table:
CREATE TABLE test.limits(RowValue text) WITH
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,
I am not sure if it is bullet proof, but could be good starting point.
Maybe someone else could find better solution:
CREATE OR REPLACE FUNCTION myschema.doCheckChanges()
RETURNS trigger AS
$BODY$
DECLARE
v_match_array BOOLEAN[];
v_match BOOLEAN;
v_row RECORD;
BEGIN
FOR v_row IN
SELECT
2012/6/8 Little, Douglas douglas.lit...@orbitz.com
Is there a postgres sql command/function that will display an object ddl?*
***
Hi,
try some from this list:
http://www.postgresql.org/docs/9.1/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE
reagrds,
Bartek
hi,
my suggestion is to redesign reporting database to fit reporting specifics
(e.g. brake normal form of database, in some cases this will speed up
reports). Than you can use some ETL tool to sync production and reporting.
Good thing is to use some OLAP software to use multidimensional analyze -
Hi, take a look at pg_class table, column relacl
http://www.postgresql.org/docs/8.2/static/catalog-pg-class.html
The opposite way (does a user has privilages to...) is set of build in
functions
http://www.postgresql.org/docs/8.2/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE
hope this
2012/4/26 Tom Lane t...@sss.pgh.pa.us
I've applied a patch for this. Thanks for the report!
regards, tom lane
Thanks for Your time :)
Regards,
Bartek
Hi,
I played with this problem few months ago and found out that
mulitidimentional cube could be a solution (
http://www.postgresql.org/docs/9.1/static/cube.html).
If You have col1 and date1, date2 then Your cube is a simple line in 2
dimensional space - axis: col1, date (line between points X,
2012/4/20 Tom Lane t...@sss.pgh.pa.us
Will look into it.
Thanks again for Your time :)
Regards,
Bartek
Hi,
according to DB theory:
*1NF: Table faithfully represents a relation and has no repeating groups*
*2NF: No non-prime attribute in the table is functionally dependent on a proper
subset of anycandidate key.*
source: http://en.wikipedia.org/wiki/Database_normalization#Normal_forms
so these
Hi,
I have create small proof of concept (pg v. 9.1.3):
1. to map Your dynamic function:
CREATE OR REPLACE FUNCTION public.testReturnDynamic(OUT retValue TEXT)
RETURNS text
AS
$BODY$
BEGIN
retValue = 'aaa';
END;
$BODY$
LANGUAGE plpgsql SECURITY DEFINER;
2. to test function
SELECT
Hi,
how about inheritance in postgres?
CREATE TABLE tblBase
(
id serial NOT NULL, -- serial type is my assumption.
SomeData integer,
CONSTRAINT tblBase_pkey PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);
CREATE TABLE tblDerived1
(
-- Inherited from table tblBase: id integer NOT NULL DEFAULT
2012/4/3 Alban Hertroys haram...@gmail.com
On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:
That is right, there is no sense to use cursors here...
I think you're wrong there: The OP is querying a system table for tables
of a certain name, which I expect can contain multiple rows for tables
I think You can use epoch
there is an example:
http://www.postgresql.org/docs/9.1/static/functions-datetime.html
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';
Regards,
Bartek
2012/4/3 Chris Angelico ros...@gmail.com
I work a lot with Unix times as integers,
, Apr 3, 2012 at 7:11 PM, Bartosz Dmytrak bdmyt...@gmail.com
wrote:
I think You can use epoch
there is an
example:
http://www.postgresql.org/docs/9.1/static/functions-datetime.html
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1
second';
Yep, but when you do
I think you need a space there:
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ' CASCADE;';
indeed, that is my fault - sorry
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table % does not exists', tablename;
It's really a pretty bad idea to
One more thing:
TRUNCATE has option CASCASE:
http://www.postgresql.org/docs/9.1/static/sql-truncate.html
I don't remember since when, but 9.X has this option.
Another thing: Do You really need this function.
AFAIK since 8.4 postgres has TRUNCATE privilage on Table
At 2012-04-04 01:15:40,Bartosz Dmytrak [via PostgreSQL] [hidden
email]http://user/SendEmail.jtp?type=nodenode=5615961i=0
wrote:
One more thing:
TRUNCATE has option CASCASE:
http://www.postgresql.org/docs/9.1/static/sql-truncate.html
I don't remember since when, but 9.X has this option
Hi,
what about this:
SELECT p.rolname, m.rolname as member, g.rolname as grantor
FROM pg_authid p
INNER JOIN pg_auth_members am ON (p.oid = am.roleid)
INNER JOIN pg_authid m ON (am.member = m.oid)
INNER JOIN pg_authid g ON (am.grantor = g.oid)
You can use proper WHERE to filter results.
That is right, there is no sense to use cursors here...
CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table % does not exists', tablename;
Hi everybody,
is there any fdw_handler for postgresql available (pg to pg)?
I saw thread
http://archives.postgresql.org/pgsql-general/2011-09/msg00525.php, need to
know if something has been changed
Thanks in advance Regards,
Bartek
thanks,
I am waiting...
Pozdrawiam,
Bartek
2012/3/23 Guillaume Lelarge guilla...@lelarge.info
On Fri, 2012-03-23 at 12:26 +0100, Bartosz Dmytrak wrote:
Hi everybody,
is there any fdw_handler for postgresql available (pg to pg)?
I saw thread
http://archives.postgresql.org/pgsql-general
Hi,
You can use one of windowing function:
http://www.postgresql.org/docs/9.1/static/tutorial-window.html
http://www.postgresql.org/docs/9.1/static/functions-window.html
this could be rank() in subquery or first_value(vale any), but there could
be performance issue
another solution could be
Hi,
there shouldn't be any problem in installing extensions to multiple
databases in the same server. Extensions are per database:
http://www.postgresql.org/docs/9.1/static/sql-createextension.html
You can use pgAdmin, or try this syntax:
CREATE EXTENSION hstore
SCHEMA public
VERSION 1.0;
Hi,
instead of
*update workorderRecord set wfstatus='failed'; *
try:
workorderRecord.wfstatus := 'failed';
I haven't tested, but workorderRecord is ROWTYPE, so shouldn't be updated
like a table.
I'm sticked to 9.1, hope the same is for 8.1
Hi,
what is the mathematical definition of this sequence?
This could be done using plpgsql, but I have to know how to calculate
values in the future.
Regards,
Bartek
2012/2/28 bbo...@free.fr
Hello!
i am again struggling with a problem i am unsure how to set up. I could
easily solve all in
.
Syntax error cleared up and loads fine but executing the stored
procedure fails to update the row.
Regards,
Patrick
--
*From:* bdmyt...@gmail.com [mailto:bdmyt...@gmail.com] *On Behalf Of *Bartosz
Dmytrak
*Sent:* Tuesday, February 28, 2012 12:24 PM
*To:* Lummis
Hi,
I am going to start with quotation: *PostgreSQL is a powerful, open
source object-relational
database system.*
So let's use objects (TYPES):
First You have to create proper types:
CREATE TYPE Facebook AS
(account_name text,
fb_special_hash text,
fb_security_hash text,
Hi,
this could be start point for discussion:
CREATE OR REPLACE FUNCTION public.arraysToHstore (IN a TEXT[], OUT c
hstore[])
RETURNS hstore[]
AS
$BODY$
DECLARE
i INT;
elements INT;
dim INT;
BEGIN
elements := array_length(a,2); -- # of elements in each dimension
dim := array_length(a,1); -- #
Maybe to show how found works and how to ignore errors - that is my
assumption only.
Regards,
Bartek
2012/2/15 Berend Tober bto...@broadstripe.net
Chris Angelico wrote:
On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrakbdmyt...@eranet.pl
wrote:
e.g. You can use BEGIN... EXCEPTION
Hi,
similar topic is in NOVICE mailing list:
http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php
e.g. You can use BEGIN... EXCEPTION END, good example of
such approach is there:
http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
;
Angelico ros...@gmail.com
On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak bdmyt...@eranet.pl
wrote:
Hi,
similar topic is in NOVICE mailing
list: http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php
e.g. You can use BEGIN... EXCEPTION END, good example of
such approach
49 matches
Mail list logo
