Re: [GENERAL] Any freeware graphic display of DDL software available?
Hi, try DBVisualizer -- Regards, Bartek
[GENERAL] How to get fully qualified names with EXPLAIN
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 - doesn't matter) I've got result: Nested Loop (cost=0.00..971583.90 rows=77571000 width=482) - Nested Loop (cost=0.00..1930.03 rows=152100 width=354) - Seq Scan on tblUnits (cost=0.00..13.90 rows=390 width=177) - Materialize (cost=0.00..15.85 rows=390 width=177) - Seq Scan on tblUnits (cost=0.00..13.90 rows=390 width=177) - Materialize (cost=0.00..17.65 rows=510 width=128) - Seq Scan on tblUnitStructure (cost=0.00..15.10 rows=510 width=128) My question is: Which tblUnits is which one? There are no fully qualified names in EXPLAIN output, so it looks impossible to determine information for particular table. Is there any workaround, maybe I miss something? any ideas, clues? regards, Bartek
Re: [GENERAL] How to get fully qualified names with EXPLAIN
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 like to do the EXPLAIN: EXPLAIN SELECT * FROM OrgStructure.tblUnits, OrgStructure.tblUnitStructure, Dictionary.tblUnits (Of course its cartesian product - doesn't matter) I've got result: Nested Loop (cost=0.00..971583.90 rows=77571000 width=482) - Nested Loop (cost=0.00..1930.03 rows=152100 width=354) - Seq Scan on tblUnits (cost=0.00..13.90 rows=390 width=177) - Materialize (cost=0.00..15.85 rows=390 width=177) - Seq Scan on tblUnits (cost=0.00..13.90 rows=390 width=177) - Materialize (cost=0.00..17.65 rows=510 width=128) - Seq Scan on tblUnitStructure (cost=0.00..15.10 rows=510 width=128) My question is: Which tblUnits is which one? There are no fully qualified names in EXPLAIN output, so it looks impossible to determine information for particular table. Is there any workaround, maybe I miss something? any ideas, clues? pls, try EXPLAIN VERBOSE postgres=# explain select * from xx.omega; QUERY PLAN ─ Seq Scan on omega (cost=0.00..34.00 rows=2400 width=4) (1 row) postgres=# explain verbose select * from xx.omega; QUERY PLAN Seq Scan on xx.omega (cost=0.00..34.00 rows=2400 width=4) Output: a (2 rows) Regards Pavel Stehule regards, Bartek
[GENERAL] effective_io_concurrency on Windows
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
Re: [GENERAL] Fwd: Functions not visible in pg_stat_user_functions view
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 visible afterwards. thanks a lot :) works as described. I think it is good idea to extend description in doc ( http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-USER-FUNCTIONS-VIEW) to cover this case. again: thank You very much for help. Regards, Bartek
[GENERAL] Fwd: Functions not visible in pg_stat_user_functions view
Hi all, Does anyone have an idea why it works like this? Regards, Bartek
Re: [GENERAL] Fwd: Functions not visible in pg_stat_user_functions view
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. Regards, Bartek
Re: [GENERAL] Fwd: Functions not visible in pg_stat_user_functions view
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 DB where some functions are not tracked, others are visible - no issue. I cannot find any logical connection between function structure and visibility in pg_stat_user_functions. Regards, Bartek
[GENERAL] Functions not visible in pg_stat_user_functions view
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 = p.prolang) WHERE stat.funcid IS NULL AND l.lanname = 'plpgsql' gives non null output (50 rows in my case) I am aware internal functions are not tracked, but in my case there are user defined functions all written in plpgsql any ideas? params: track_functions=all PostgreSQL v. 9.2.2 on Windows 2008R2 (64bit) Regards, Bartek
Re: [GENERAL] logs encoding problem Windows
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 encoding FROM pg_database gives 6 for all DBs). Is that encoding You think about? datcollate and datctype are Polish Poland.1250 for all DBs Regards, Bartek
Re: [GENERAL] ERROR: query has no destination for result data
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 to return the SELECT statement. Ho can I achieve this? DO $$ DECLARE v_some_id int=14; BEGIN /* more queries here... */ SELECT 'this is text'; END $$ LANGUAGE plpgsql; Best, Peter Kroon
Re: [GENERAL] Dropping all foreign keys for a column in a table
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_schema_name variable to function parameters
or table OID as p_table_name, and then get table and schema name (fully
qualified) from casting oid to regclass:
e.g.
SELECT 'pg_class'::regclass::oid
gives me: 1259
and
SELECT 1259::regclass
gives me: pg_class
You can try this with any table and second casting will give You fully
qualified name besed on provided OID.
Regards,
Bartek
2012/8/29 Andreas Joseph Krogh andr...@officenet.no
Here is a function for removing all FKs on a column (yes, PG for some
reason allows multiple similar FKs on a column):
create or replace function remove_fk_by_table_and_column(**p_table_name
varchar, p_column_name varchar) returns INTEGER as $$
declare
v_fk_name varchar := NULL;
v_fk_num_removed INTEGER := 0;
begin
FOR v_fk_name IN (SELECT ss2.conname
FROM pg_attribute af, pg_attribute a,
(SELECT conname, conrelid,confrelid,conkey[i] AS conkey,
confkey[i] AS confkey
FROM (SELECT conname, conrelid,confrelid,conkey,**confkey,
generate_series(1,array_upper(**conkey,1)) AS i
FROM pg_constraint WHERE contype = 'f') ss) ss2
WHERE af.attnum = confkey
AND af.attrelid = confrelid
AND a.attnum = conkey
AND a.attrelid = conrelid
AND a.attrelid = p_table_name::regclass
AND a.attname = p_column_name) LOOP
execute 'alter table ' || quote_ident(p_table_name) || ' drop
constraint ' || quote_ident(v_fk_name);
v_fk_num_removed = v_fk_num_removed + 1;
END LOOP;
return v_fk_num_removed;
end;
$$ language plpgsql;
Usage:
select remove_fk_by_table_and_column(**'my_table', 'some_column');
I find myself often having to remove FK-constraints on a column because
they are refactored to point to other columns or whatever, and I thought
this might be useful to others.
--
Andreas Joseph Kroghandr...@officenet.no - mob: +47 909 56 963
Senior Software Developer / CEO - OfficeNet AS - http://www.officenet.no
Public key:
http://home.officenet.no/~**andreak/public_key.aschttp://home.officenet.no/~andreak/public_key.asc
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL limitations question
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. I think it is a matter of description quality, not real limitations - they are huge, but... exists. Regards, Bartek
Re: [GENERAL] How do write schema independent install files for functions.
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 RETURN 42; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 100; CREATE OR REPLACE FUNCTION another_schema.bar() RETURNS INTEGER AS $BODY$ BEGIN RETURN my_schema.foo(); END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 100; then script is readable and uses full qualified names. http://www.postgresql.org/docs/9.1/static/ddl-schemas.html#sql-createschema.html Regards, Bartek
Re: [GENERAL] PostgreSQL limitations question
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: *Any customer can have a car painted any color that he wants so long as it is black.* Number of indexes is unlimited until it is limited by pg_class table size (regardless free HD space). Regards, Bartek
Re: [GENERAL] PostgreSQL limitations question
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 via shell script loop myself. Alternately, you could use the COPY support of the DB drivers in perl or Python to do it. this time it doesn't matter - agree COPY is better, this is only one time 3. do Vacuum full to be sure free space is removed VACUUM FULL test.limits; Which version of Pg are you running? If it's older than 9.0 you're possibly better off using CLUSTER instead of VACUUM FULL. I am sorry - 9.1.4 Use pg_total_relation_size to include TOAST tables too. it doesn't metter - conclusion is: table is growing. You are right, for other purposes it should be better to check total size. Regards, Bartek
Re: [GENERAL] PostgreSQL limitations question
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 number of indexes per table. Reason is the same. Indexes are stored in table pg_class (relkind = 'i'), so when we agree number of table rows is limited, then number of indexes is limited too. There is fair sentence for number of columns - depending on column type. I think there should be an explanation what *unlimited* really means. Thanks for Your attention. Regards, Bartek
[GENERAL] PostgreSQL limitations question
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=FALSE, FILLFACTOR=100);
2. Fill table (I used pgScript available in pgAdmin);
DECLARE @I;
SET @I = 0;
WHILE @I 1000
BEGIN
INSERT INTO test.limits (RowValue) VALUES (NULL);
SET @I = @I + 1;
END
3. do Vacuum full to be sure free space is removed
VACUUM FULL test.limits;
4. I checked table size:
SELECT * FROM pg_size_pretty(pg_relation_size('test.limits'::regclass));
and I realized table size is 32 kB.
I used pgstattupet extension (
http://www.postgresql.org/docs/9.1/static/pgstattuple.html) to check what
is going on:
SELECT * FROM pgstattuple('test.limits');
and I got:
table_len tuple_count tuple_len tuple_percent dead_tuple_count
dead_tuple_len dead_tuple_percent free_space free_percent 32768 1000 24000
73.24 0 0 0 4608 14.06
Did I missed something?
Is there a non storage cost data type?
I know that storage requirement for a short string (up to 126 bytes) is 1
byte plus the actual string (
http://www.postgresql.org/docs/9.1/static/datatype-character.html).
Regards,
Bartek
Re: [GENERAL] View parsing
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 (d.objid = rw.oid) WHERE rw.ev_class = 'MySchema.MyViewName'::regclass ORDER BY n.nspname, c.relname, a.attname This will answer for all involved tables and columns - not only produced by a view, but also involved in joins and sub-queries. Assumption: rules are deprecated and used only by PG internal engine (for views). If this assumption is not correct You should narrow pg_rewrite results to find proper rule. Reagards, Bartek
Re: [GENERAL] TG_COLUMNS_UPDATED
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 attname FROM pg_attribute WHERE attrelid = (quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME))::text::regclass AND attnum 0 ORDER BY attnum LOOP EXECUTE 'SELECT NOT ($1.' || quote_ident(v_row.attname) || ' = $2.' || quote_ident(v_row.attname) || ')' INTO v_match USING NEW, OLD; v_match_array = array_append (v_match_array, v_match); END LOOP; RAISE NOTICE 'array: %', (array_to_string(v_match_array, ',')); RETURN NEW; END; $BODY$ LANGUAGE plpgsql SECURITY DEFINER assumption: this is on update trigger function - You could extend this code to check trigger conditions and do what You want to do with v_match_array. Regards, Bartek 2012/7/3 david.sahag...@emc.com I would like another TG_* special variable to be available to a PL/pgSQL trigger-function. TG_COLUMNS_UPDATED Its value would be NULL unless: TG_OP == ' UPDATE' and TG_LEVEL == 'ROW' Data type == varbit One bit for each column of the table that the trigger is created on. 1 means that the column was in the set clause of the update statement that made the trigger fire 0 means it was not I understand that CREATE TRIGGER already has UPDATE [ OF column_name [, ... ] ] Is this a relatively straightforward enhancement ? It would allow me to know whether various timestamp columns in the row were unlucky enough to have been set to the same exact value already existing in the table *versus* were simply not set by the UPDATE statement. Thanks, -dvs-
Re: [GENERAL] describe command for
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
Re: [GENERAL] Up-to-date reports database
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 - this will make queries easier (with MDX language). I think this kind of discussion is huge one :) there are some opensource ETL and BI suits available. Regards, Bartek
Re: [GENERAL] Table / View Security Report
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 helps Regards, Bartek
Re: [GENERAL] Explain verbose query with CTE
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
Re: [GENERAL] How do I setup this Exclusion Constraint?
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 and X, Y2), if you have col1, col2 and date1 and date2 then Your cube is in 3 dimensional space (axis: col1, col2, date), and so on. You have to be sure that those cubes (lines even points!) are separete, eg. distance is greater then 0 - this really depends on requirements: is it possible that date ranges stick together, like continuous period of time divided into 2? if Yes then distance could be 0 but intersection is still 0 You have to think about this. You can build GIST index on cube function to be sure that exclusion check is fast. For sure this is not the only one solution, maybe others will find more easy way - I am really interested in simpler solution. regards, Bartek
Re: [GENERAL] Explain verbose query with CTE
2012/4/20 Tom Lane t...@sss.pgh.pa.us Will look into it. Thanks again for Your time :) Regards, Bartek
Re: [GENERAL] Feature Proposal: Constant Values in Columns or Foreign Keys
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 constants are not in line with this approach. You can implement one to one relation: CREATE TABLE tblBase( id text NOT NULL, SomeData integer, CONSTRAINT tblBase_pkey PRIMARY KEY (id ) ); CREATE TABLE tblDerived1( id text NOT NULL, Data1 integer, CONSTRAINT tblDerived1_pkey PRIMARY KEY (id ), CONSTRAINT tblDerived1_id_fkey FOREIGN KEY (id) REFERENCES tblBase (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE tblDerived2( id text NOT NULL, Data1 text, CONSTRAINT tblDerived2_pkey PRIMARY KEY (id ), CONSTRAINT tblDerived2_id_fkey FOREIGN KEY (id) REFERENCES tblBase (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ); * * So, You don't have any duplicated PK, every class is described in separate entity (table). If Your business logic needs to recognize classes in other way You can use views, with constant field which is not stored on disk: CREATE VIEW vDerived1 AS SELECT *, 'DERIVED1'::text as ClassType FROM tblBase NATURAL JOIN tblDerived1; CREATE VIEW vDerived2 AS SELECT *, 'DERIVED2'::text as ClassType FROM tblBase NATURAL JOIN tblDerived2; * * The problem is that tblDerived1.id is not guaranteed to be not present in tblDerived2. This could be handled e.g. by trigger (before update) on both tables (cross check), or using a kind of middleware, I mean a function which is responsible do perform inserts. In my opinion it should be possible to recognize proper class based on its attributes, so it should be quite easy to implement this function. Function could be overloaded (same name, different set of attributes). Regards, Bartek
Re: [GENERAL] EXECUTE command-string INTO target USING expression isn't working
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.testReturnDynamic(); 3. to do the shortcut of Your loop DO $$ DECLARE t TEXT; routine TEXT; dynSQL TEXT; BEGIN routine = 'public.testReturnDynamic'; dynSQL = 'SELECT * FROM ' || routine || '();'; EXECUTE dynSQL INTO t; RAISE NOTICE 'OUTPUT: %', t; END; $$ and received: NOTICE: OUTPUT: aaa so, works as expected. Maybe one of Your functions has more then one column, or returns something different then text? message in error looks like problem inside executed procedure. Regards, Bartek 2012/4/18 Ken Winter k...@sunward.org I swear this used to work, but in PostgreSQL 9.1 it doesn't work any more... CASE 1: If I write it like this: FOR func IN ( SELECT * FROM information_schema.routines WHERE routine_schema = 'tests' ) LOOP q := 'SELECT tests.' || func.routine_name || '()'; EXECUTE q INTO r; ... END LOOP; on the first time through the loop I get this error: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function cre_supers_for_organization_i line 12 at SQL statement SQL statement INSERT INTO organization (name, status) VALUES (str, 'Closed Ongoing Group') PL/pgSQL function event line 32 at SQL statement SQL statement SELECT tests.event() PL/pgSQL function run_all_tests line 16 at EXECUTE statement SQL state: 42601 CASE 2: If I write it like this: FOR func IN ( SELECT * FROM information_schema.routines WHERE routine_schema = 'tests' ) LOOP q := 'SELECT tests.$1()'; EXECUTE q INTO r USING func.routine_name; ... END LOOP; on the first time through the loop I get this error: ERROR: syntax error at or near $1 LINE 1: SELECT tests.$1() ^ QUERY: SELECT tests.$1() CONTEXT: PL/pgSQL function run_all_tests line 17 at EXECUTE statement SQL state: 42601 In both cases, each of the functions to be called returns a string, and r is a VARCHAR variable. What's wrong with this picture? ~ Thanks in advance for your help ~ Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature Proposal: Constant Values in Columns or Foreign Keys
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
nextval('tblBase_id_seq'::regclass),
-- Inherited from table tblBase: SomeData integer,
Data1 integer,
CONSTRAINT tblDerived1_pkey PRIMARY KEY (id )
)
INHERITS (tblBase)
WITH (
OIDS=FALSE
);
CREATE TABLE tblDerived2
(
-- Inherited from table tblBase: id integer NOT NULL DEFAULT
nextval('tblBase_id_seq'::regclass),
-- Inherited from table tblBase: SomeData integer,
Data2 text,
CONSTRAINT tblDerived2_pkey PRIMARY KEY (id )
)
INHERITS (tblBase)
WITH (
OIDS=FALSE
);
inheritance is described in doc here:
http://www.postgresql.org/docs/9.1/static/ddl-inherit.html
With this approach all IDs will use the same sequence so there will not
be duplicated PKs in inherited tables.
This could be also modeled with standard SQL approach
without redundant information. Solution depends on requirements.
Regards,
Bartek
2012/4/17 Nils Gösche car...@cartan.de
Hi!
I have a little feature proposal. Let me try to explain the motivation
behind it.
Suppose our application has two types of objects, looking somewhat like
this:
abstract class Base
{
public int Id;
public int SomeData;
}
class Derived1 : Base
{
public int Data1;
}
class Derived2 : Base
{
public string Data2;
}
There are many ways of modeling this in a relational database. I am
interested in this one:
CREATE TYPE derived_type AS ENUM ('derived1', 'derived2);
CREATE TABLE base (
id int PRIMARY KEY,
some_data int NOT NULL,
type derived_type NOT NULL
);
CREATE UNIQUE INDEX base_derived_type_index ON base (id, derived_type);
CREATE TABLE derived1 (
id int PRIMARY KEY,
data1 int NOT NULL,
type derived_type NOT NULL CHECK (type = 'derived1'),
FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
)
CREATE TABLE derived2 (
id int PRIMARY KEY,
data2 text NOT NULL,
type derived_type NOT NULL CHECK (type = 'derived2'),
FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
)
Note that the type column in derived1 and derived2 ensures that there is at
most one row in either derived1 or derived2 which refers to a given row in
base.
This works fine, actually. What bugs me, though, is the redundant data in
the type columns of derived1 and derived2. It would be nice if I could
either declare the columns as constant (so the data wouldn't be stored on
disk anymore), or (even better) use a constant value in the foreign keys,
as
in
FOREIGN KEY (id, 'derived2') REFERENCES base (id, type) ON DELETE
CASCADE
In the latter case, I could omit the type column of derived1 and derived2
altogether.
I suspect that it wouldn't be terribly hard to implement this. What do you
think? Wouldn't this be nice to have?
Regards,
--
Nils Gösche
Don't ask for whom the CTRL-G tolls.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me to take a look of the erros in my functions. Thanks.
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 of
the same name in different schema's.
Of course, that may not be applicable to the her situation.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
hmm...
if tablename variable contains schema name this function will never work,
because:
quote_ident ('aa.aaa') gives aa.aaa what is not proper fully qualified
name, should be aa.aaa.
So, my assumption is tablename variable contains only table name. If this
is only table name, without schema name then postgre will try to truncate
table only in schema where this table could be found (according to
search_path parameter). It is not possible to have more then one table with
the same name in the same schema.
Grace wrote:
*I tried to create function to truncate table*
this drives me to think about one table not all of them in database, but
cursor statement could be misleading.
I think it is not a good idea to truncate all tables with the same name in
all schemas (maybe this is Grace's intention - don't know).
BTW, *tablename *column of *pg_catalog.pg_tables* view contains only table
name without schema, so this statement will NOT truncate all tables with
the same name accross all schemas because of search_path.
http://www.postgresql.org/docs/9.1/static/view-pg-tables.html
Regards,
Bartek
Re: [GENERAL] Cast timestamptz to/from integer?
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, but would like to store them in Postgres as 'timestamp(0) with time zone' for convenience and readability. Unfortunately the syntax to translate between the two is a little cumbersome, so I'm looking at hiding it away behind a function - or a cast. However, the CREATE CAST docs say that I have to be the owner of either the source or target types, which presumably means I can't define a cast from timestamptz to int. Is there a workaround for this? Chris Angelico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cast timestamptz to/from integer?
There is a build in function which encapsulates that statement: SELECT to_timestamp (982384720); EXPLAIN ANALYZE shows: Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1 loops=1) so this looks cheap Regards, Bartek 2012/4/3 Chris Angelico ros...@gmail.com On Tue, 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 that a lot, your statement gets extremely long. I can create a function that'll hide the mess away, but what I'm hoping to do is simply cast: SELECT 982384720::timestamptz; ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.
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 print your own message instead of using the system's message. In this case, you would have figured out the problem immediately if you'd seen the real error message, which was presumably bleating about t1cascade. Like always, it depends, custom error message has been required by Grace -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Regards, Bartek
Re: [GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.
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/docs/9.1/static/sql-grant.html
this is not the same as DELETE so, I think it is enough to grant this
privilage to user
Regards,
Bartek
2012/4/3 leaf_yxj leaf_...@163.com
Tom,
Thanks. I found out the key issue it. It's because the truncate command
can't have the cascade.
For the other people reference. The right funcitons are :
***
CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table % does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;
***
***
CREATE OR REPLACE FUNCTION truncate_t (tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table % does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;
***
usage : select truncate_t ('aaa');
Thanks everybody's help.
Regards.
Grace
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Please help me to take a look of the erros in my functions. Thanks.
If You mean parent and child tables as connected by relation (primery key -
foreign key) then child table will be truncated regardless the relation
type, if CASCADE exists.
This applies to PG 9.1.3 (I've got only this version).
Regards,
Bartek
2012/4/3 leaf_yxj leaf_...@163.com
Hi Bartek
One more question, In oracle, when you create table using the default
option, the parent table can't be delete if there is any child table exist.
Usually, I won't use the cascade option. I will truncate or delete one by
one. what is postgresql default for these???
Thanks.
Regards.
Grace
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.
Another thing: Do You really need this function.
AFAIK since 8.4 postgres has TRUNCATE privilage on Table
http://www.postgresql.org/docs/9.1/static/sql-grant.html
this is not the same as DELETE so, I think it is enough to grant this
privilage to user
Regards,
Bartek
2012/4/3 leaf_yxj [hidden
email]http://webmail.mail.163.com/user/SendEmail.jtp?type=nodenode=5615860i=0
Tom,
Thanks. I found out the key issue it. It's because the truncate command
can't have the cascade.
For the other people reference. The right funcitons are :
***
CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table % does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;
***
***
CREATE OR REPLACE FUNCTION truncate_t (tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table % does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;
***
usage : select truncate_t ('aaa');
Thanks everybody's help.
Regards.
Grace
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list ([hidden
email]http://webmail.mail.163.com/user/SendEmail.jtp?type=nodenode=5615860i=1
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
If you reply to this email, your message will be added to the discussion
below:
http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615860.html
To unsubscribe from Please help me to take a look of the erros in my
functions. Thanks., click here.
NAMLhttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
--
View this message in context: Re:Re: Please help me to take a look of the
erros in my functions.
Thanks.http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615961.html
Sent from the PostgreSQL - general mailing list
archivehttp://postgresql.1045698.n5.nabble.com/PostgreSQL-general-f1843780.htmlat
Nabble.com.
Re: [GENERAL] How to check the role has been granted to which role. Help me to double check . Thanks.
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. Regards, Bartek 2012/3/31 leaf_yxj leaf_...@163.com I want to check the role has been granted to which role. In my working environment, the all the normal is assigned to role group. when i issue dp, it only give me the role group privilege. So I need to check which user is in which user group. THe following is my sql to do that. Is there anybody has a better way to do it. Thanks. Grace select DISTINCT user, group_name, grantor, admin_option from (select usename AS user,roleid,admin_option from pg_user join pg_auth_members on ( pg_user.usesysid=pg_auth_members.member)) a, (select usename AS group_name,roleid from pg_user join pg_auth_members on (pg_user.usesysid=pg_auth_members.roleid)) b, (select usename AS grantor,roleid from pg_user join pg_auth_members on (pg_user.usesysid=pg_auth_members.grantor)) c where a.roleid=b.roleid and b.roleid=c.roleid; member | group_name | grantor | admin_option ++-+-- user1 | grace | gpadmin | f user_1 | grace | gpadmin | f (2 rows) -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-check-the-role-has-been-granted-to-which-role-Help-me-to-double-check-Thanks-tp5608906p5608906.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me to take a look of the erros in my functions. Thanks.
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; END; $$ LANGUAGE plpgsql SECURITY DEFINER STRICT; this works fine for me. Regards, Bartek 2012/4/2 Pavel Stehule pavel.steh...@gmail.com Hello IF stmt IN statements then is nonsense. use trapping exceptions instead BEGIN EXECUTE 'TRUNCATE TABLE ' || quote_ident(_tablename) || ' CASCADE'; EXCEPTION WHEN undefined_table THEN RAISE EXCEPTION 'your own exception, when you like'; END; Regards Pavel 2012/4/2 leaf_yxj leaf_...@163.com: I tried to create function to truncate table 1) when the user call the function just specify the tablename 2) the user can use the function owner privilege to execute the function. But I got the errors as follows. Please help me to take a look. Thanks. Regards. Grace -- function : CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void AS $$ DECLARE stmt RECORD; statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables; BEGIN IF stmt IN statements then EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;'; ELSE The tablename doesn't exist.doesn END IF ; END; $$ LANGUAGE 'plpgsql' security definer; errors. ERROR: syntax error at or near $2 LINE 1: SELECT $1 IN $2 ^ QUERY: SELECT $1 IN $2 CONTEXT: SQL statement in PL/PgSQL function truncate_t near line 6 -- View this message in context: http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5613507.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql_fdw_handler
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
Re: [GENERAL] postgresql_fdw_handler
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/2011-09/msg00525.php, need to know if something has been changed Not yet. There is one in the latest commitfest, still in Needs review status (https://commitfest.postgresql.org/action/patch_view?id=667). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GROUP BY or alternative means to group
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 boolean flag default in table address_reference which should be unique for single company, I mean value true should be unique - this could be reached by unique partial index on column* *refid_companies with condition default = true http://www.postgresql.org/docs/9.1/static/indexes-partial.html#INDEXES-PARTIAL-EX3 hope Your pg version supports windowing functions (as I remember 8.4 and above) Of course there is a solution with subquery which finds min id in table addresses of each refid_companies in table addresses_reference and this subquery is joined with companies table, but I am afraid this is not the best one. Regards, Bartek 2012/3/12 Alexander Reichstadt l...@mac.com Hi, the following statement worked on mysql but gives me an error on postgres: *column addresses.address1 must appear in the GROUP BY clause or be used in an aggregate function* I guess I am doing something wrong. I read the web answers, but none of them seem to meet my needs: *SELECT companies.id,companies.name,companies.organizationkind,addresses.address1,addresses.address2,addresses.city,addresses.zip FROM companies JOIN addresses_reference ON companies.id=addresses_reference.refid_companies LEFT JOIN addresses ON addresses_reference.refid_addresses=addresses.idGROUP BY companies.id;* What I did now was create a view based on above statement but without grouping. This returns a list with non-distinct values for all companies that have more than one address, which is correct. But in some cases I only need one address and the problem is that I cannot use distinct. I wanted to have some way to display a companies list that only gives me the first stored addresses related, and disregard any further addresses. Is there any way to do this? Thanks Alex
Re: [GENERAL] Single server multiple databases - extension
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; if hstore is installed in public schema, sometimes You have to use public.hstore syntax (fully qualified name) - this depends on your search_path setting. in your example it looks like hstore is installed, but question is: where is it? You can find this info using SQL like this one: SELECT * FROM pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid) http://www.postgresql.org/docs/9.1/static/catalog-pg-extension.html this could be useful too: SELECT * FROM pg_available_extension_versions WHERE name = 'hstore' http://www.postgresql.org/docs/9.1/static/view-pg-available-extension-versions.html Regards, Bartek 2012/3/6 Brian Trudal dbrb2002-...@yahoo.com Any one know how to install extensions to multiple databases in the same server ? Thanks in advance Brian -- *From:* Brian Trudal dbrb2002-...@yahoo.com *To:* pgsql-general@postgresql.org pgsql-general@postgresql.org *Sent:* Monday, March 5, 2012 4:52 PM *Subject:* Single server multiple databases - extension Hi I have 2 databases running in a single server; and I installed extension 'hstore' to one database and it works fine. When I tried to use the same extension in another database, it gives an error saying 'extension does not exist'; nor it allow to install as it complains about its existence. Any help ? db1=# CREATE EXTENSION hstore; ERROR: type hstore already exists db1=# DROP EXTENSION hstore; ERROR: extension hstore does not exist db1=# create table foo(id hstore); ERROR: type hstore is only a shell LINE 1: create table foo(id hstore); ^
Re: [GENERAL] Stored Procedure Record Updates using For Loops - Postgres 8.1
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-declarations.html According to doc for 8.3 it looks the same ( http://www.postgresql.org/docs/8.3/static/plpgsql-declarations.html), so should work. Regards, Bartek 2012/2/28 Lummis, Patrick J p...@dolby.com ** Hi, I'm trying to update a record within a for loop and at the point of updating I get the following syntax error: ERROR: syntax error at or near $1 LINE 1: update $1 set wfstatus='failed' ^ QUERY: update $1 set wfstatus='failed' CONTEXT: SQL statement in PL/PgSQL function workorder_status_integrity_check near line 13 ** Error ** ERROR: syntax error at or near $1 SQL state: 42601 Context: SQL statement in PL/PgSQL function workorder_status_integrity_check near line 13 Below is the procedure in question using Postgres 8.1: CREATE OR REPLACE FUNCTION workorder_status_integrity_check() RETURNS integer AS $$ DECLARE workorderRecord workorder%ROWTYPE; declare counter int DEFAULT 0; BEGIN FOR workorderRecord IN SELECT * from workorder LOOP IF workorderRecord.wfstatus = 'canceled' THEN counter = counter +1; ELSEIF workorderRecord.wfstatus = 'finished' THEN counter = counter +1; ELSE update workorderRecord set wfstatus='failed'; END IF; END LOOP; RETURN counter; END; $$ LANGUAGE plpgsql; Thanks, Patrick
Re: [GENERAL] how to create data on the fly?
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 the php backend, but this would impede further extensions and doesn't satisfy my curiosity :D so, here's the problem: a patient takes everyday a medecine, and from time to time comes in a result of a blood-sample. Now i first of all, i want to draw a graph showing the dosis taken, and the blood-values mesured. Later on i want to calculate the assimilation rate, the saturation rate, and the effective rate, both in real, and prognosis My actual problem beeing that the dosis may be not simply 1 to n pills per day, but for example day 1 1, day 2 1.25, day 3 .5, day 4 1, etc... the most complex case to now i, has a period over 4 days so i set up: CREATE TABLE patients ( id integer NOT NULL, name text, minzone real, maxzone real, refresh integer ); CREATE TABLE inrdata ( id integer NOT NULL, temps timestamp without time zone, patid integer, inr real ); CREATE TABLE posologie ( id integer NOT NULL, inrid integer, champ text, definition text, valeur real ); In patients i have the persons name, and the boundaries that are wanted for the blood-sample value, in inrdata i have the timepoints where i get a blood-sample-result, and i eventually adjust the posology. in posologie i have the table at a timepoint i have stored at the moment in champ=sequence, definition=1;1.25;0.5;1, and actually i solve the problem with the frontend what i would like is to store the posology iterations in the posology table, and be able to make a select that generates the data on the fly. Taking the different waypoints given by the data in inrdata as starting point and computing for each day the actual dosis but i have no idea how to do this in sql? any help appreciated! ciao Bruno == bbo...@adlp.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored Procedure Record Updates using For Loops - Postgres 8.1
Hi, I suppose the the workorderRecord IS updated, but You expect workorder table row to be updated :) if so, function snipped should be like this: ... ELSE UPDATE workorder SET wfstatus = 'failed' WHERE workorder.primary_key = workorderRecord.primary_key; ... this will update workorder table row which corresponds to workorderRecord. workorderRecord is not exactly the reference to workorder table row (like in JAVA), but it is rather a separate copy. regards, Bartek 2012/2/28 Lummis, Patrick J p...@dolby.com ** Hi Bartek, Thanks for 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 Dmytrak *Sent:* Tuesday, February 28, 2012 12:24 PM *To:* Lummis, Patrick J *Cc:* pgsql-general@postgresql.org *Subject:* Re: [GENERAL] Stored Procedure Record Updates using For Loops - Postgres 8.1 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-declarations.html According to doc for 8.3 it looks the same ( http://www.postgresql.org/docs/8.3/static/plpgsql-declarations.html), so should work. Regards, Bartek 2012/2/28 Lummis, Patrick J p...@dolby.com ** Hi, I'm trying to update a record within a for loop and at the point of updating I get the following syntax error: ERROR: syntax error at or near $1 LINE 1: update $1 set wfstatus='failed' ^ QUERY: update $1 set wfstatus='failed' CONTEXT: SQL statement in PL/PgSQL function workorder_status_integrity_check near line 13 ** Error ** ERROR: syntax error at or near $1 SQL state: 42601 Context: SQL statement in PL/PgSQL function workorder_status_integrity_check near line 13 Below is the procedure in question using Postgres 8.1: CREATE OR REPLACE FUNCTION workorder_status_integrity_check() RETURNS integer AS $$ DECLARE workorderRecord workorder%ROWTYPE; declare counter int DEFAULT 0; BEGIN FOR workorderRecord IN SELECT * from workorder LOOP IF workorderRecord.wfstatus = 'canceled' THEN counter = counter +1; ELSEIF workorderRecord.wfstatus = 'finished' THEN counter = counter +1; ELSE update workorderRecord set wfstatus='failed'; END IF; END LOOP; RETURN counter; END; $$ LANGUAGE plpgsql; Thanks, Patrick
Re: [GENERAL] How to store variable data in a field?
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_extended_hash text);
CREATE TYPE Twitter AS
(account_name text,
hash1 bigint, --assume this is bigint not text
megahash text);
Next You have to modify Your table to handle types:
ALTER TABLE public.account ADD COLUMN TweeterInfo Twitter;
ALTER TABLE public.account ADD COLUMN FacebookInfo Facebook;
there is nothing special, You simply use Your types as column types.
Next few examples of using types:
insert into table
INSERT INTO public.account(
id_account, id_account_type, n_account, account_details,
comment,
TweeterInfo, FacebookInfo)
VALUES (1, 2, 'ABc', 'test', 'test comment',
ROW('xpto', 3432454355, 'dfcf786fds987fds897'), ROW('xpto', 'dsdsad4535',
'dsadsad454355', 'sdasfe5r4536556fsgg'));
And SELECT examples:
SELECT *
FROM public.account
WHERE (account.TweeterInfo).account_name = 'xpto';
SELECT (account.TweeterInfo).megahash
FROM public.account
WHERE (account.TweeterInfo).account_name = 'xpto';
You can find more in doc:
http://www.postgresql.org/docs/9.1/static/rowtypes.html
http://www.postgresql.org/docs/9.1/static/sql-createtype.html
To be noticed:
using hstore is good idea too - this type is dedicated to be used with
key-value pairs with existing set of build-in functions.
Custom type gives You possibility to create table of this type or function
returning this type as row or set of rows.
More SQL oriented solution is to create one/two new tables to store these
values, or extend table account with few new columns (if all of them are
required).
Regards,
Bartek
2012/2/21 Thomas Kellerer spam_ea...@gmx.net
Andre Lopes, 21.02.2012 16:11:
Hi all,
I need to create a table to store Twitter accounts information,
Facebook and other social networks details for API login. I need to
know if it is possible to store the account details(account_details
field) in a field that contains something like an array. I need to
store this data in an array like field because the details for each
social network accounts are different. What is my best choice for the
field account_details?
CREATE TABLE account (
id_account int4 NOT NULL,
id_account_type int4 NOT NULL,
n_account varchar(50) NOT NULL,
account_details varchar NOT NULL,
comment varchar(2000),
PRIMARY KEY(id_account,id_account_**type)
);
I will need to store something like this:
Twitter: array(account_name = xpto, hash1 = 3432454355,
megahash = dfcf786fds987fds897)
Facebook: array(account_name = xpto, fb_special_hash =
dsdsad4535, fb_security_hash = dsadsad454355,
fb_extended_hash = sdasfe5r4536556fsgg)
It is possible to put something like that in a field? If yes, what
datatype should I choose? Pros and cons of doing this?
Use the hstore datatype. That's exactly what you are looking for
http://www.postgresql.org/**docs/current/static/hstore.**htmlhttp://www.postgresql.org/docs/current/static/hstore.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to dereference 2 dimensional array?
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); -- # of dimensions
a := string_to_array(array_to_string(a, '|', 'NULL'), '|', 'NULL');
--rewrite multidimensional array into single dimensional
FOR i IN 0..(dim -1) LOOP --loop throug all dimensions to create hsore
array (0 is for first elment in rewrited array)
c[i+1] = hstore (a[(i*elements)+1: (i+1)*elements]); --create hstore array
element using part of array a, this part (window) is moving while loop is
evaluated)
END LOOP;
END
$BODY$
LANGUAGE plpgsql SECURITY DEFINER IMMUTABLE STRICT;
--usage example
SELECT (public.arraysToHstore('{{f1, 1, f3, 123, f4, ABC},
{f5, 2, f6, 345, f7, DEF}}')::hstore[])[2]
This works for me (PostgreSQL 9.1.2).
You can pass whatever text array You want (any size, but 2 dimensions only)
and You will receive 1 dimentional hstore array with number of elements
corresponding to input array's dimensions)
Regards,
Bartek
2012/2/16 ChoonSoo Park luisp...@gmail.com
I would like to construct hstore array from 2 dimensional array.
For example,
'{{f1, 1, f2, 123, f3, ABC, ...}, {f1, 2, f2, 345, f3, DEF, ...}}'
Should be converted to
2 hstore values
f1=1, f2=123, f3=ABC, ...
f2=2, f2=345, f3=DEF, ...
create or replace function convertHStore(p1 text[][]) RETURNS hstore[]
hstore function requires text[] to convert array to hstore. Therefore I
should be able to dereference 2 dimensional array element.
Inside this custom plpgsql function, p1[1] is not valid syntax to
dereference the 1st element in p1.
Anyone knows how to solve this problem?
Thank you,
Choon Park
Re: [GENERAL] Easy form of insert if it isn't already there?
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 END, good example of such approach is there: http://www.postgresql.org/**docs/9.1/static/plpgsql-** control-structures.html#**PLPGSQL-UPSERT-EXAMPLEhttp://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE ; I wonder why, in that example, you would not try the INSERT first, and if that fails, then do the update? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Easy form of insert if it isn't already there?
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 ; Regards, Bartek 2012/2/15 Chris Angelico ros...@gmail.com Periodically I find myself wanting to insert into some table, specifying the primary key column(s), but to simply ignore the request if it's already there. Currently I have two options: 1) Do the insert as normal, but suppress errors. SAVEPOINT foo; INSERT INTO table (col1,col2,col3) VALUES (val1,val2,val3); (if error) ROLLBACK TO SAVEPOINT foo; 2) Use INSERT... SELECT: INSERT INTO table (col1,col2,col3) SELECT val1,val2,val3 WHERE NOT EXISTS (SELECT * FROM table WHERE col1=val1 AND col2=val2) The former makes unnecessary log entries, the latter feels clunky. Is there some better way? All tips appreciated! Chris Angelico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Easy form of insert if it isn't already there?
Yes it is. You can implement trigger on table to check if inserted record is new. Still it is on DB side. I don't know PHP well enough but I think You can call function e.g. SELECT myschema.InsertWhenNew (val1, val2, val3); in the same way as You call INSERTS Regards, Bartek 2012/2/15 Chris 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 is there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE ; Ah, thanks for that! Currently the query is a single PHP pg_query_params() call, and it's inside a larger transaction. By the look of it, this requires writing a function to do the job, rather than embedding the logic straight into the query - is this correct? ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
