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
;
Reg
You
call INSERTS
Regards,
Bartek
2012/2/15 Chris Angelico
> On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak
> wrote:
> > Hi,
> > similar topic is in NOVICE mailing
> > list: http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php
> >
> > e.g. You c
Maybe to show how "found" works and how to ignore errors - that is my
assumption only.
Regards,
Bartek
2012/2/15 Berend Tober
> Chris Angelico wrote:
>
>> On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak
>> wrote:
>>
>>>
>>> e.g. You
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); -- #
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,
fb_ex
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
http://www.postgresql.org/docs/9.1/static/plpgsql-declara
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
> Hello!
>
> i am again struggling with a problem i am unsure how to set up. I could
> easily solve all in the php bac
r the quick response.
>
> 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
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,
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 boolea
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
> 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.postgresq
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.
Regard
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
2012/4/3 Alban Hertroys
> 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 multipl
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
> I work a lot with Unix times as integers, but would like to
t 7:11 PM, Bartosz Dmytrak
> 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
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 b
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
http://www.postgresql.org/do
Thanks.
>
> Regards.
>
> Grace
>
>
> At 2012-04-04 01:15:40,"Bartosz Dmytrak [via PostgreSQL]" <[hidden
> email]<http://user/SendEmail.jtp?type=node&node=5615961&i=0>>
> wrote:
>
> One more thing:
> TRUNCATE has option CASCASE:
&
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 NUL
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 const
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 public.tes
2012/4/20 Tom Lane
> Will look into it.
>
>
Thanks again for Your time :)
Regards,
Bartek
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 h
2012/4/26 Tom Lane
>
> 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, Y1
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 -
t
2012/6/8 Little, Douglas
> 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,
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
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 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 (OIDS=FAL
2012/7/12 Craig Ringer
>
>> 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 via shell script
2012/7/12 David Johnston
>
>
> 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 number o
2012/7/13 Chris Angelico
>
>
> 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:
*"Any custo
2012/7/16 Philip Couling
>
> 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
RETURN 42;
END;
$
2012/7/26 Bruce Momjian
>
> 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. I think it i
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 p_
38 matches
Mail list logo