Re: [GENERAL] Starting a cluster as a service

[Léa Massiot]

Hello Thomas,
Contrary to what you say, I provided command lines and messages (in case of
failure).
What is missing according to you?
Thank you and best regards.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728p5713039.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] full text index / search

[Albe Laurenz]

Philipp Kraus wrote:
> I have created a table with a text field under PG 9.1, that should
store source codes. I would like to
> search in this text field with regular expressions. I think I need a
full-text-index, do I?
> How can I create this index, do I need some additional extensions? The
PG server runs under OSX
> (installed on the DMG package).

A full text index won't help you with regular expressions.
It will only help with full text search using the match operator @@.

Yours,
Laurenz Albe

-- 
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] full text index / search

[Tomas Vondra]

On 18 Červen 2012, 11:04, Albe Laurenz wrote:
> Philipp Kraus wrote:
>> I have created a table with a text field under PG 9.1, that should
> store source codes. I would like to
>> search in this text field with regular expressions. I think I need a
> full-text-index, do I?
>> How can I create this index, do I need some additional extensions? The
> PG server runs under OSX
>> (installed on the DMG package).
>
> A full text index won't help you with regular expressions.
> It will only help with full text search using the match operator @@.

Also, it's possible to create regular index with custom operator class for
pattern matching queries. See this

http://www.postgresql.org/docs/9.1/interactive/indexes-opclass.html

Not a full-text index, though ...

T.


-- 
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] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[utsav]

Thanks for reply but you have used SQL as a language . 

Please give me Plpgsql example because  i am facing problem in plpgsql only
..


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/RETURNING-MORE-THAN-ONE-CUSTOM-TYPE-FROM-FUNCTION-tp5712546p5713064.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] Starting a cluster as a service

[Léa Massiot]

Hi again,

It looks like the problem comes from the "-l" option I'm trying to set when
I register the service.

1) If I register the cluster as a service in the following way:

It works: the service is automatically started properly.

2) If I add the following option:

and then try to start the service (via "Control Panel" -> "Administrative
Tools" -> "Services"), it fails with the message:


Is this not the right way to set the cluster log file?
Or maybe this should be set in the cluster "postgresql.conf" configuration
file instead?

Thanks for helping.
Best regards.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728p5713066.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] Starting a cluster as a service

[Alban Hertroys]

On 18 June 2012 09:46, Léa Massiot  wrote:
> Hello Thomas,
> Contrary to what you say, I provided command lines and messages (in case of
> failure).
> What is missing according to you?

The command lines. If you did indeed post them, they did not make it
to the list.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
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] Starting a cluster as a service

[Léa Massiot]

Sorry. I added some "< raw >" tags so maybe this is the reason why you
couldn't see half of my message.

 

Hi again,

It looks like the problem comes from the "-l" option I'm trying to set when
I register the service.

1) If I register the cluster as a service in the following way:

---
dos ; a_user> pg_ctl.exe register -N a_pgcluster_srv -U a_user -P a_password
-D "a_pgcluster" -S auto -w -t 60
---

It works: the service is automatically started properly.

2) If I add the following option:

---
-o "-l '/a_pgcluster.log'"
---

and then try to start the service (via "Control Panel" -> "Administrative
Tools" -> "Services"), it fails with the message:

---
"The a_pgcluster_srv service on Local Computer started and then stopped. 
Some services stop automatically if they have no work to do, for example,
the Performance Logs and Alerts service."
---


Is this not the right way to set the cluster log file?
Or maybe this should be set in the cluster "postgresql.conf" configuration
file instead?

Thanks for helping.
Best regards.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728p5713070.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] Get RULE condition and commands

[Vlad Arkhipov]

On 06/16/2012 02:20 AM, Tom Lane wrote:

Vlad Arkhipov  writes:

What is the proper way of getting RULE condition and commands?

pg_get_ruledef()

regards, tom lane

It prints the whole CREATE RULE command. Is there any way to extract 
WHERE condition of the rule?


--
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] array vs bit fields for masking

[Sergey Konoplev]

Hi,

On Sun, Jun 17, 2012 at 7:03 PM, Chip Nowacek  wrote:
> configuration at the row level. My initial thinking was to have a bit field
> for each quality: version BIT(20), config(20). Each view would mask based on
> those fields. Then (multi-dimensional) arrays crossed my mind. Any
> performance / maintainability thoughts in using arrays vs bit fields?

If you intended to use this fields with partial indexes only
(http://www.postgresql.org/docs/9.1/static/indexes-partial.html) then
you will benefit in size. Otherwise I would advise to use arrays as
the are more feature rich and indexable (take a note of this
http://www.postgresql.org/docs/9.1/static/intarray.html).

Also look at the hstore
(http://www.postgresql.org/docs/9.1/static/hstore.html). You might
find it interesting to store the states in a more flexible way. Pay
attention to the index support in this case too.

-- 
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204

-- 
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] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[Merlin Moncure]

On Mon, Jun 18, 2012 at 4:30 AM, utsav  wrote:
> Thanks for reply but you have used SQL as a language .
>
> Please give me Plpgsql example because  i am facing problem in plpgsql only

create function f(foo out foo, bar out bar) returns setof
record as $$
begin
 return query select (v, v::text)::foo, (v, v::text)::bar from
generate_series(1,3) v;
end
$$ language plpgsql;

 -- or --

create or replace function f(foo out foo, bar out bar) returns setof
record as $$
begin
 f.foo = (1,'a')::foo;
 f.bar = (2,'b')::bar;
 return next;
end
$$ language plpgsql;

(in older versions of postgres you might have to be a little more
careful about names of input and output arguments).

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to include Tablefunc as an extension

[Stefan Schwarzer]

Hello,

 

I read through the Postgres doc and many Google results, but it seems still 
unclear to me how to include additional packages into my postgres database. I 
see that there are a few installed under 
"/usr/local/pgsql-9.1/share/extension/" (I am on Lion and installed the 
Kyngchaos libs). But as I want to install crosstab now, I downloaded the source 
code for postgres, run a make/install - and now wonder what to do with it. I 
could eventually load the .sql file as usual; but it seems this should be now 
avoided, and loaded as an extension. I tried to copy three files (--1.0.sql, 
--unpackaged.sql and the normal .sql) to the folder and then load it from 
PgAdmin, but this results in an error message ("could not access file 
"$libdir/tablefunc"").

 

Thanks for any hints!

Re: [GENERAL] How to include Tablefunc as an extension

[Tomas Vondra]

On 18 Červen 2012, 16:29, Stefan Schwarzer wrote:
> Hello,
>
>
>
> I read through the Postgres doc and many Google results, but it seems
> still unclear to me how to include additional packages into my postgres
> database. I see that there are a few installed under
> "/usr/local/pgsql-9.1/share/extension/" (I am on Lion and installed the
> Kyngchaos libs). But as I want to install crosstab now, I downloaded the
> source code for postgres, run a make/install - and now wonder what to do
> with it. I could eventually load the .sql file as usual; but it seems this
> should be now avoided, and loaded as an extension. I tried to copy three
> files (--1.0.sql, --unpackaged.sql and the normal .sql) to the folder and
> then load it from PgAdmin, but this results in an error message ("could
> not access file "$libdir/tablefunc"").

This should do the trick:

CREATE EXTENSION crosstab;


T.


-- 
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] How to include Tablefunc as an extension

[Chris Travers]

On Mon, Jun 18, 2012 at 7:36 AM, Tomas Vondra  wrote:
> On 18 Červen 2012, 16:29, Stefan Schwarzer wrote:
>> Hello,
>>
>>
>>
>> I read through the Postgres doc and many Google results, but it seems
>> still unclear to me how to include additional packages into my postgres
>> database. I see that there are a few installed under
>> "/usr/local/pgsql-9.1/share/extension/" (I am on Lion and installed the
>> Kyngchaos libs). But as I want to install crosstab now, I downloaded the
>> source code for postgres, run a make/install - and now wonder what to do
>> with it. I could eventually load the .sql file as usual; but it seems this
>> should be now avoided, and loaded as an extension. I tried to copy three
>> files (--1.0.sql, --unpackaged.sql and the normal .sql) to the folder and
>> then load it from PgAdmin, but this results in an error message ("could
>> not access file "$libdir/tablefunc"").
>
CREATE EXTENSION tablefunc;

(we use connectby() in LedgerSMB 1.3)

Best Wishes,
Chris Travers

-- 
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] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[utsav]

-- Table: bar

-- DROP TABLE bar;

CREATE TABLE bar
(
  barid integer,
  barsubid integer,
  barname text
)
WITH (
  OIDS=FALSE
);
ALTER TABLE bar
  OWNER TO postgres;

-- Table: foo

-- DROP TABLE foo;

CREATE TABLE foo
(
  fooid integer,
  foosubid integer,
  fooname text
)
WITH (
  OIDS=FALSE
);
ALTER TABLE foo
  OWNER TO postgres;




-- Function: getallfoobar()

-- DROP FUNCTION getallfoobar();

CREATE OR REPLACE FUNCTION getallfoobar3(foo OUT foo,bar OUT bar)
  RETURNS SETOF record AS
$BODY$
 DECLARE
 r foo%rowtype;
 r1 bar%rowtype;
 BEGIN
 FOR r IN SELECT * FROM foo
 WHERE fooid > 3
 LOOP
 -- can do some processing here
  RAISE NOTICE 'r == %',r; 
 -- return next row of SELECT
 END LOOP;

 FOR r1 IN SELECT * FROM bar
 WHERE barid > 0
 LOOP
 -- can do some processing here
  -- return next row of SELECT
 RAISE NOTICE 'r1 == %',r1; 
 END LOOP;
 RETURN NEXT;
 END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
---

select * from getallfoobar3();


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/RETURNING-MORE-THAN-ONE-CUSTOM-TYPE-FROM-FUNCTION-tp5712546p5713131.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] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[utsav]

I am getting null in output.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/RETURNING-MORE-THAN-ONE-CUSTOM-TYPE-FROM-FUNCTION-tp5712546p5713132.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] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[Merlin Moncure]

On Mon, Jun 18, 2012 at 12:04 PM, utsav  wrote:
> -- Table: bar
>
> -- DROP TABLE bar;
>
> CREATE TABLE bar
> (
>  barid integer,
>  barsubid integer,
>  barname text
> )
> WITH (
>  OIDS=FALSE
> );
> ALTER TABLE bar
>  OWNER TO postgres;
> 
> -- Table: foo
>
> -- DROP TABLE foo;
>
> CREATE TABLE foo
> (
>  fooid integer,
>  foosubid integer,
>  fooname text
> )
> WITH (
>  OIDS=FALSE
> );
> ALTER TABLE foo
>  OWNER TO postgres;
>
> 
>
>
> -- Function: getallfoobar()
>
> -- DROP FUNCTION getallfoobar();
>
> CREATE OR REPLACE FUNCTION getallfoobar3(foo OUT foo,bar OUT bar)
>  RETURNS SETOF record AS
> $BODY$
>  DECLARE
>     r foo%rowtype;
>     r1 bar%rowtype;
>  BEGIN
>     FOR r IN SELECT * FROM foo
>     WHERE fooid > 3
>     LOOP
>         -- can do some processing here
>              RAISE NOTICE 'r == %',r;
>         -- return next row of SELECT
>     END LOOP;
>
>     FOR r1 IN SELECT * FROM bar
>     WHERE barid > 0
>     LOOP
>         -- can do some processing here
>          -- return next row of SELECT
>         RAISE NOTICE 'r1 == %',r1;
>     END LOOP;
>     RETURN NEXT;
>  END
> $BODY$
>  LANGUAGE plpgsql VOLATILE
>  COST 100
>  ROWS 1000;
> ---
>
> select * from getallfoobar3();

you're getting null results because you never assigned anything to
your output variables.  'RETURN NEXT' will emit a new record for both
OUT foo and OUT bar based on whatever they are containing at the time.
 Try running my example above and extending it.

merlin

-- 
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] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[utsav]

-- Function: getallfoobar()

-- DROP FUNCTION getallfoobar();

CREATE OR REPLACE FUNCTION getallfoobar3(foo OUT foo,bar OUT bar)
  RETURNS SETOF record AS
$BODY$
 DECLARE
 r foo%rowtype;
 r1 bar%rowtype;
  
BEGIN
 FOR r IN SELECT * FROM foo
 WHERE fooid > 3
 LOOP
 -- can do some processing here
  RAISE NOTICE 'r == %',r; 
 -- return next row of SELECT'
 getallfoobar3.foo = r;
 END LOOP;

 FOR r1 IN SELECT * FROM bar
 WHERE barid > 0
 LOOP
 -- can do some processing here
  -- return next row of SELECT
 RAISE NOTICE 'r1 == %',r1; 
 END LOOP;
getallfoobar3.bar = r1;
 RETURN NEXT;
 END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;


/Thanks for your help ../

*But still i want output in record here i am getting only last record in
ouput  ...*

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/RETURNING-MORE-THAN-ONE-CUSTOM-TYPE-FROM-FUNCTION-tp5712546p5713149.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] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[Merlin Moncure]

On Mon, Jun 18, 2012 at 1:33 PM, utsav  wrote:
> -- Function: getallfoobar()
>
> -- DROP FUNCTION getallfoobar();
>
> CREATE OR REPLACE FUNCTION getallfoobar3(foo OUT foo,bar OUT bar)
>  RETURNS SETOF record AS
> $BODY$
>  DECLARE
>     r foo%rowtype;
>     r1 bar%rowtype;
>
> BEGIN
>     FOR r IN SELECT * FROM foo
>     WHERE fooid > 3
>     LOOP
>         -- can do some processing here
>              RAISE NOTICE 'r == %',r;
>         -- return next row of SELECT'
>         getallfoobar3.foo = r;
>     END LOOP;
>
>     FOR r1 IN SELECT * FROM bar
>     WHERE barid > 0
>     LOOP
>         -- can do some processing here
>          -- return next row of SELECT
>         RAISE NOTICE 'r1 == %',r1;
>     END LOOP;
>    getallfoobar3.bar = r1;
>     RETURN NEXT;
>  END
> $BODY$
>  LANGUAGE plpgsql VOLATILE
>  COST 100
>  ROWS 1000;
>
>
> /Thanks for your help ../
>
> *But still i want output in record here i am getting only last record in
> ouput  ...*

sure -- you're only calling one 'return next'.  you need to call
return next for each row you want to return.

you've also got two loops -- that isn't going to work as intended.
your code should be structured like this:

FOR 
LOOP
  
  foo := f;
  
  bar := b;
  RETURN NEXT;
END LOOP;

If you want heterogeneously sized lists to be returned from a single
function, you might want to consider returning arrays, not a set
returning function.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Examples of "dblink_build_sql_update"

[vmkurz]

Hallo im new on this. 
I need know how to use "dblink_build_sql_update" . Examples please.

I want know how to use "dblink_build_sql_update" using 2 database from 2
diferent host, i can insert , delete but cant update. Please help me with
few examples.

This is an example of INSERT. 


Thanks for your time


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Examples-of-dblink-build-sql-update-tp5713107.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

[GENERAL] Composite Types, arrays, and functions

[Chris Schnaufer]

I am getting an "invalid input syntax for type double precision" error when I 
return an array as part of a composite type from my C-language function. I can 
re-create this situation with just a type and a function.

My type is declared as follows:

CREATE TYPE my_grid AS (min_x double precision, min_y double precision, max_x 
double precision, max_y double precision, my_arr double precision[][]);

My function is defined as:

CREATE FUNCTION my_test()
RETURNS void AS $$
DECLARE
mine  my_grid;
BEGIN

SELECT INTO mine (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double 
precision[][])::my_grid;
END
$$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public;


To get the error, I run the following query:

SELECT MY_TEST();

If I do a plain "SELECT (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double 
precision[][])::my_grid;" everything looks good.

Any help would be appreciated.

NOTICE: This message is covered by the Electronic Communications Privacy Act, 
Title 18, United States Code, Sections 2510-2521. This e-mail and any attached 
files are the exclusive property of Pictometry International Corp., are deemed 
privileged and confidential, and are intended solely for the use of the 
individual(s) or entity to whom this e-mail is addressed. If you are not one of 
the named recipient(s) or believe that you have received this message in error, 
please delete this e-mail and any attachments and notify the sender 
immediately. Any other use, re-creation, dissemination, forwarding or copying 
of this e-mail is strictly prohibited and may be unlawful.

[GENERAL] retrieving function raise messages in ecpg embedded sql code

[Haszlakiewicz, Eric]

I'm trying to get some additional information back from a trigger to my 
embedded SQL
program, to essentially emulate Informix's way of generating serial values.
I can get the serial to be generated, but I'm trying to figure out how to get 
the
generated value back to my program with minimal changes to the SQL.

I have a trigger that looks a bit like this:

create table mytable (mycol integer, mycol2 integer);

create or replace function functionfoo() returns trigger as $QUOTED$
BEGIN
new.mycol = nextval(TG_TABLE_NAME || '_mycol_seq');
raise INFO using MESSAGE = 'A Message';
return new;
END;
$QUOTED$ LANGUAGE 'plpgsql';

create trigger mytable_insert_trig before insert on mytable for each row when 
(new.mycol = 0) execute procedure functionfoo();


My ecpg program looks a bit like this:

exec sql begin declare section;
long mycol1;
long mycol2;
const char *mydb;
exec sql end declare section;
mycol1 = 0;
mycol2 = 1;
mydb = "mydb";
exec sql connect to :mydb;
exec sql prepare row_insert from "insert into mytable values (?, ?)";
EXEC SQL EXECUTE row_insert using :mycol1, mycol2;


I can't figure out how to retrieve the message raised by the trigger.  I know 
it's 
available in some cases, because I see the message when I insert a row through 
psql,
but even things like this:
   printf("%s\n", PQerrorMessage(ECPGget_PGconn(mydb)));

return nothing useful.  Is there a way to get this information?

Thanks,
eric


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Fine-grained replication?

[Paul Jones]

Is anyone aware of other non-trigger-based, fine-grained replication tools for 
PostgreSQL along the lines of the XReader 
 or pgreplay 
?

Thanks,
Paul Jones


-- 
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] Composite Types, arrays, and functions

[Merlin Moncure]

On Mon, Jun 18, 2012 at 4:23 PM, Chris Schnaufer
 wrote:
> I am getting an “invalid input syntax for type double precision” error when
> I return an array as part of a composite type from my C-language function. I
> can re-create this situation with just a type and a function.
>
>
>
> My type is declared as follows:
>
>
>
> CREATE TYPE my_grid AS (min_x double precision, min_y double precision,
> max_x double precision, max_y double precision, my_arr double
> precision[][]);
>
>
>
> My function is defined as:
>
>
>
> CREATE FUNCTION my_test()
>
> RETURNS void AS $$
>
> DECLARE
>
> mine  my_grid;
>
> BEGIN
>
>
>
>     SELECT INTO mine (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double
> precision[][])::my_grid;
>
> END
>
> $$
>
> LANGUAGE plpgsql
>
> SECURITY DEFINER
>
> SET search_path = public;
>
>
>
>
>
> To get the error, I run the following query:
>
>
>
> SELECT MY_TEST();
>
>
>
> If I do a plain “SELECT (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double
> precision[][])::my_grid;” everything looks good.
>
>
>
> Any help would be appreciated.

This highly unfortunate behavior is coming form the fact that pl/pgsql
assuming you want to pass a list of fields, not a constructed row
type, when assigning with INTO.

This would work:
SELECT INTO mine ((0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double
precision[][])::my_grid).*;

so would this:
mine := (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double precision[][])::my_grid;

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Craig Ringer]

Hi all

I've been working in psql a lot recently, and have started to wonder why 
statements with syntax errors or other problems that render them 
unexecutable terminate the transaction.


I understand why statements that raise errors during their execution 
terminate a transaction, and that explicit savepoints may be used if 
this is undesired. That's all good, and I know that 
ON_ERROR_ROLLBACK=interactive provides a helper for that in psql.


Savepoints are overhead, though, and I don't understand why they're 
required for statements that don't even parse. If I typo a statement and 
run:


   SELETC blah FROM blah;

why is a savepoint required to stop that from terminating the 
transaction? I know psql isn't parsing and validating the statements so 
bad statements still go to the backend, of course, but I don't get why 
the backend can't recognise an unparseable statement or statement that 
references non-existent database objects and report it without killing 
the transaction if it's talking to psql interactively.


Is this just a "nobody's cared enough to implement it" thing, where it'd 
be possible but the simplest/safest/easiest path is to have the backend 
always kill the tx and nobody's wanted to add a communication channel to 
let psql tell the backend it's working interactively?


--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

--
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 discussion: Should syntax errors abort a transaction?

[Tom Lane]

Craig Ringer  writes:
> I've been working in psql a lot recently, and have started to wonder why 
> statements with syntax errors or other problems that render them 
> unexecutable terminate the transaction.

Well, the obvious reason is that it's hard to tell what the user meant,
so bailing is the safest response.

> I understand why statements that raise errors during their execution 
> terminate a transaction,

So you're suggesting that "SELECT 1/0;" should terminate a transaction,
but "SELECT 1//0;" should not?  How about "ROLBACK;"?  It gets pretty
squishy pretty fast when you try to decide which sorts of errors are
more important than others.

regards, tom lane

-- 
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] How to include Tablefunc as an extension

[Stefan Schwarzer]

>> I read through the Postgres doc and many Google results, but it seems
>> still unclear to me how to include additional packages into my postgres
>> database. I see that there are a few installed under
>> "/usr/local/pgsql-9.1/share/extension/" (I am on Lion and installed the
>> Kyngchaos libs). But as I want to install crosstab now, I downloaded the
>> source code for postgres, run a make/install - and now wonder what to do
>> with it. I could eventually load the .sql file as usual; but it seems this
>> should be now avoided, and loaded as an extension. I tried to copy three
>> files (--1.0.sql, --unpackaged.sql and the normal .sql) to the folder and
>> then load it from PgAdmin, but this results in an error message ("could
>> not access file "$libdir/tablefunc"").
> 
> This should do the trick:
> 
> CREATE EXTENSION crosstab;

Thanks a lot. Yes, that's the command I used when it launches the above error 
message. The question is more about: "where do I need to place the tablefunc 
files (and which ones) in order to execute successfully that command"?

Thanks for any hints.
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general