[GENERAL] RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

[utsav]

I am doing Oracle to PostgreSQL migration activity as part of Procedure
Migration in Oracle there are *OUT parameters which return records(using
bulk collect) of custom type.*

*like function returing type1,type2. *

What will be alternative for PostgreSQL to do this.

*There are OUT parameters in PostgreSQL but i am not able to set returns set
of type1,type2 .
*
Appreciate your Help.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/RETURNING-MORE-THAN-ONE-CUSTOM-TYPE-FROM-FUNCTION-tp5712546.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] Is there a way to ask PostgreSQL for the name of the computer it's running on?

[Rob Richardson]

My customer has 3 computers.  The PostgreSQL service could be running on either 
of two of them.  There is currently no way in our system to determine which one 
it is running on.  The third computer sometimes needs to know which of the 
other two computers is active.  It would be enough to know which computer is 
running the PostgreSQL service.  Is the name of the server available in a 
PostgreSQL database, so that it could be retrieved using a query?

The computers are running Windows Server 2003.

Thank you very much.

RobR

Re: [GENERAL] pg_upgrade: "pg_ctl failed to start the new server"

[Bruce Momjian]

On Wed, Jun 13, 2012 at 10:41:37PM -0400, Evan D. Hoffman wrote:
> Actually I found the solution right after I sent that email (of
> course):
>
> https://wiki-bsse.ethz.ch/download/attachments/55283107/PostgreSQL_9_M
> aintenance_Backup_and_Recovery_final.docx
>
> Has to do with the order in which shared libs are loaded when both 9.0
> and 9.1 are installed.  Renaming the config file as described in that
> doc resolved it.

I didn't find out which file you renamed.  Why would the server show as
started while pg_ctl -w returned an error?  I am guessing that you could
not connect somehow.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

[GENERAL] Azure?

[Craig Ringer]

Hi all

I was at a Microsoft Azure event yesterday (hey, don't blame me, the 
beer was on Microsoft) and was surprised by how open Azure appears to be.


Given the reports of unsatisfactory to miserable performance I see here 
from people running Pg on EC2, I'm curious about whether anyone's fired 
up an Azure VM (Linux or Windows) and done some tests, focusing 
particularly on crash-safety, fsync rates, and I/O performance. If not, 
I might have to have a play.


--
Craig Ringer

--
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] pg_upgrade: "pg_ctl failed to start the new server"

[Bruce Momjian]

On Wed, Jun 13, 2012 at 11:19:41AM -0400, Evan D. Hoffman wrote:
> I'm trying to upgrade Postgres 9.0 to 9.1 with pg_upgrade.  Both
> versions are installed from the PGDG Yum repo:
> 
> -bash-4.1$ /usr/pgsql-9.0/bin/postgres -V
> postgres (PostgreSQL) 9.0.8
> -bash-4.1$ /usr/pgsql-9.1/bin/postgres -V
> postgres (PostgreSQL) 9.1.4
> 
> I can successfully start and connect to both 9.0 and 9.1.  When I run
> pg_upgrade, it fails with the error "pg_ctl failed to start the new
> server," however it apparently does start the new server (output
> below).  Any ideas?  This is a freshly installed CentOS 6.2 machine
> with the 9.0 DB copied over from another machine via streaming
> replication (which I have since ended and the server is now
> standalone).

Have you looked at the logs from pg_upgrade?  Does "pg_ctl -w start"
work?  My guess is that the -w (wait) is failing for some reason.  We
did improve the -w handling in PG 9.1 so I didn't think there were any
failure cases, but you might have found a new one.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] import *.backup-file (PostGIS - not mine)

[gipsy-king1]

I think my postgis is installed because of these three following reasons -
but I am not 100% sure. 

1) With pgAdmin I created a database. The template I took was the
"template_postgis"
2) When I open the Application Stack Builder an get the list of all possible
applications to install - under "Spataial Extensions" it is written:
"PostGIS 1.5 for PostgreSQL 8.3 v 1.5.3(installed)
3) I followed the instructions from this website to install PostGIS:
http://www.gpsfiledepot.com/tutorials/installing-and-setting-up-postgresql-with-postgis/

How can I check if PostGIS is 100% installed on my PC? 

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/import-backup-file-could-not-access-file-libdir-liblwgeom-No-such-file-or-directory-tp5712030p5712345.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] Recovery continually requests new WAL files

[Alex Good]

On 13/06/12 09:10, Albe Laurenz wrote:

Alex Good wrote:

I have a simple setup with one master and one backup server. I have an
issue where I have performed a backup and copied it to the data
directory for the slave, written a recovery.conf and copied in the
backup_label file and then started the server, it happily restores
everything up until and including the WAL file mentioned in the
backup_label and then attempts to obtain the next archive file which

has

not yet been archived. I can't for the life of me figure out what is
going on.

What else would you expect?

Are you planning to use streaming replication?

If yes, what are your configuration parameters for replication?

Yours,
Laurenz Albe
What I expected to see was the server requesting each WAL file up until 
the one which was archived during pg_stop_backup and then the server 
would consider itself to be recovered. Clearly I have misunderstood 
something here.


These two servers are actually sat behind pgpool which is in replication 
mode (so I don't have streaming replication set up) which I chose 
beccause it gives me synchronous replication as well as automatic 
failover. I am trying to understand the recovery process so I can use it 
to set up pgpools' online recovery feature.


Thanks
Alex Good

Alex

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

[GENERAL] pg_upgrade: "pg_ctl failed to start the new server"

[Evan D. Hoffman]

I'm trying to upgrade Postgres 9.0 to 9.1 with pg_upgrade.  Both
versions are installed from the PGDG Yum repo:

-bash-4.1$ /usr/pgsql-9.0/bin/postgres -V
postgres (PostgreSQL) 9.0.8
-bash-4.1$ /usr/pgsql-9.1/bin/postgres -V
postgres (PostgreSQL) 9.1.4

I can successfully start and connect to both 9.0 and 9.1.  When I run
pg_upgrade, it fails with the error "pg_ctl failed to start the new
server," however it apparently does start the new server (output
below).  Any ideas?  This is a freshly installed CentOS 6.2 machine
with the 9.0 DB copied over from another machine via streaming
replication (which I have since ended and the server is now
standalone).

Thanks.



# su - postgres
-bash-4.1$ ps ax | grep postg
 4354 pts/0S  0:00 su - postgres
 4389 pts/0S+ 0:00 grep postg
-bash-4.1$ /usr/pgsql-9.1/bin/pg_upgrade -c -b /usr/pgsql-9.0/bin/ -B
/usr/pgsql-9.1/bin/ -d /var/lib/pgsql/9.0/data/ -D
/var/lib/pgsql/9.1/data/
Performing Consistency Checks
-
Checking current, bin, and data directories ok
Checking cluster versions   ok
Checking database user is a superuser   ok
Checking for prepared transactions  ok
Checking for reg* system oid user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok

pg_ctl failed to start the new server
Failure, exiting
-bash-4.1$ ps ax | grep postg
 4354 pts/0S  0:00 su - postgres
 4445 pts/0S  0:00 /usr/pgsql-9.1/bin/postgres -D
/var/lib/pgsql/9.1/data -p 5432 -b
 4446 ?Ss 0:00 postgres: logger process
 4448 ?Ss 0:00 postgres: writer process
 4449 ?Ss 0:00 postgres: wal writer process
 4450 ?Ss 0:00 postgres: stats collector process
 4453 pts/0S+ 0:00 grep postg

-- 
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] Passing master tag around in a multi-site master-slave system

[John R Pierce]

On 06/13/12 3:25 PM, Gauthier, Dave wrote:
One master, 4 slaves.  Can only write to the master (over WAN).  No 
write transaction can be committed until it's duplicated at all the 
slave sites.  (this, so far, is I think a standard 
requirement/request).  Now, the "master" token can get passed from one 
site to the other depending on the viability of the communications 
between the sites.  If site A was the master but went down, the 
remaing 4 should be smart enough to detect this and decide who becomes 
the new master.  If site A became isolated, it ought to detect that it 
can't communicate with the other sites and that it needs to put itself 
into read_only mode.


what if A (initial master) and B are isolated from C, D, E (maybe a 
transpacific link gets broken, so continents are isolated).  does C,D,E 
decide its a quorum and promotes one to master, while A decides they 
lost too many peers so demotes itself to offline? or does A assume that 
B is sufficient slave, and retains master status, unknowingly while C 
has been promoted too?


all schemes like this are frigging complicated.

oh yeah, and a 5-way synchronous commit across a global WAN is going to 
be SLOW!





--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

[GENERAL] Passing master tag around in a multi-site master-slave system

[Gauthier, Dave]

Here's the problem...

Five sites, one DB, all 5 sites have read/write to the DB.  If one site goes 
down, the other 4  should be able to continue to work with the DB (read and 
write).  When the dead site comes back on line, it ought to be able to rejoin 
the group.  If one site become isolated (not "down", just maybe network 
issues), it ought to go to read-only mode until it can rejoin as a member of 
the group.

I was thinking...

One master, 4 slaves.  Can only write to the master (over WAN).  No write 
transaction can be committed until it's duplicated at all the slave sites.  
(this, so far, is I think a standard requirement/request).  Now, the "master" 
token can get passed from one site to the other depending on the viability of 
the communications between the sites.  If site A was the master but went down, 
the remaing 4 should be smart enough to detect this and decide who becomes the 
new master.  If site A became isolated, it ought to detect that it can't 
communicate with the other sites and that it needs to put itself into read_only 
mode.

All this should be automatic.  Would be willing to code up solutions to detect 
network viability, pass the "master" token around, etc... .  Would like to know 
if a basic 5-site (1 master, 4 slaves) config is possible/sensible/viable.  If 
so, what sw/solution would be best?  (all linux-linux, WAN access, 
inter-continental/global)

Re: [GENERAL] ctid ranges

[Tom Lane]

Bruce Momjian  writes:
> On Wed, Jun 13, 2012 at 03:21:17PM -0500, Merlin Moncure wrote:
>> IMNSHO, it's a no-brainer for the todo (but I think it's more
>> complicated than adding some comparisons -- which are working now):

> I see.  Seems we have to add index smarts to those comparisons.  That
> might be complicated.

Uh, the whole point of a TID scan is to *not* need an index.

What would be needed is for tidpath.c to let through more kinds of TID
comparison quals than it does now, and then for nodeTidscan.c to know
what to do with them.  The latter logic might well look something like
btree indexscan qual preparation, but it wouldn't be the same code.

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

[GENERAL] composite type use in pl/gpsql

[Little, Douglas]

Merlin writes"
first, the way to do insert from composite type is like this:

insert into foo select (f).*;

if f is type of foo.  The actual error you're getting is probably"


I gave this a try.  Still bumping into syntax errors
CREATE TYPE oww_mart_tbls.type_log_site_process AS
   (proc_id integer,
proc_name character varying(500),
step_id integer,
step_desc character varying(2000),
step_starttime timestamp without time zone,
step_endtime timestamp without time zone,
step_returncode character varying(500),
activity_count integer,
status_flag character varying(1),
status_desc character varying(2000));
ALTER TYPE oww_mart_tbls.type_log_site_process
  OWNER TO gpadmin;

Create function ...
DECLARE
-- standard variables
vSpReturn_code text;
Logsp oww_mart_tbls.type_log_site_process%ROWTYPE;
.

insert into oww_mart_tbls.log_site_process(proc_id, proc_name, step_id, 
step_desc, step_starttime, step_endtime, step_returncode, activity_count, 
status_flag, status_desc)
 select (logsp).*;

works.
Thanks for the help.


Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CD497A.68F0C620]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] ctid ranges

[Bruce Momjian]

On Wed, Jun 13, 2012 at 03:21:17PM -0500, Merlin Moncure wrote:
> On Wed, Jun 13, 2012 at 3:18 PM, Bruce Momjian  wrote:
> > On Wed, Jun 13, 2012 at 03:15:14PM -0500, Merlin Moncure wrote:
> >> yeah -- and I think it's a great thing to want to be able to do.  it
> >> could be used in parallelizing tricks for example: divide up a table
> >> into N approximately equal parts and hand each one off to a work
> >> thread.
> >
> > Can we add this as a TODO?  It would basically be adding
> > less/greater-than comparisons for the 'tid' data type.
> 
> IMNSHO, it's a no-brainer for the todo (but I think it's more
> complicated than adding some comparisons -- which are working now):
> 
> postgres=# explain select ctid from foo where ctid >= '(3786,67)'::tid limit 
> 1;
> QUERY PLAN
> --
>  Limit  (cost=0.00..0.05 rows=1 width=6)
>->  Seq Scan on foo  (cost=0.00..16422.00 rows=33 width=6)
>  Filter: (ctid >= '(3786,67)'::tid)
> (3 rows)

I see.  Seems we have to add index smarts to those comparisons.  That
might be complicated.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] ctid ranges

[Merlin Moncure]

On Wed, Jun 13, 2012 at 3:18 PM, Bruce Momjian  wrote:
> On Wed, Jun 13, 2012 at 03:15:14PM -0500, Merlin Moncure wrote:
>> yeah -- and I think it's a great thing to want to be able to do.  it
>> could be used in parallelizing tricks for example: divide up a table
>> into N approximately equal parts and hand each one off to a work
>> thread.
>
> Can we add this as a TODO?  It would basically be adding
> less/greater-than comparisons for the 'tid' data type.

IMNSHO, it's a no-brainer for the todo (but I think it's more
complicated than adding some comparisons -- which are working now):

postgres=# explain select ctid from foo where ctid >= '(3786,67)'::tid limit 1;
QUERY PLAN
--
 Limit  (cost=0.00..0.05 rows=1 width=6)
   ->  Seq Scan on foo  (cost=0.00..16422.00 rows=33 width=6)
 Filter: (ctid >= '(3786,67)'::tid)
(3 rows)

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] ctid ranges

[Bruce Momjian]

On Wed, Jun 13, 2012 at 03:15:14PM -0500, Merlin Moncure wrote:
> On Mon, Jun 11, 2012 at 7:57 PM, Jeff Davis  wrote:
> > On Fri, 2012-06-08 at 22:27 +0100, Thomas Munro wrote:
> >> This is slow, handled with a seq scan (as are various rephrasing with
> >> <, <=, etc):
> >>
> >> SELECT ... FROM ... WHERE ctid BETWEEN ... AND ...;
> >>
> >> Is there a way to retrieve the rows in a physical range quickly?
> >
> > Interesting idea. However, as far as I know, there is no such support.
> 
> yeah -- and I think it's a great thing to want to be able to do.  it
> could be used in parallelizing tricks for example: divide up a table
> into N approximately equal parts and hand each one off to a work
> thread.

Can we add this as a TODO?  It would basically be adding
less/greater-than comparisons for the 'tid' data type.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] ctid ranges

[Merlin Moncure]

On Mon, Jun 11, 2012 at 7:57 PM, Jeff Davis  wrote:
> On Fri, 2012-06-08 at 22:27 +0100, Thomas Munro wrote:
>> This is slow, handled with a seq scan (as are various rephrasing with
>> <, <=, etc):
>>
>> SELECT ... FROM ... WHERE ctid BETWEEN ... AND ...;
>>
>> Is there a way to retrieve the rows in a physical range quickly?
>
> Interesting idea. However, as far as I know, there is no such support.

yeah -- and I think it's a great thing to want to be able to do.  it
could be used in parallelizing tricks for example: divide up a table
into N approximately equal parts and hand each one off to a work
thread.

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] composite type use in pl/gpsql

[Merlin Moncure]

On Wed, Jun 13, 2012 at 12:25 PM, Little, Douglas
 wrote:
> Hello,
>
> Thanks in advance for taking my question.
>
> Running on Greenplum 4.1.2/ based on PG 8.2.3 kernal
>
>
>
> We make extensive use of functions to do our ETL.
>
> So, I’m building a stored procedure template for our developers.
>
> I’d like the template to log the sql statements to a logging table for
> audit/debug purposes.  Statements are logged after execution so we have a
> completion code.
>
> I’d also like to have an exception block for each statement – so prior steps
> get committed.
>
>
>
> Since out procedures can have a large number of steps  <20,  I’d like to
> avoid repeating the exception and logging code.
>
> In PL/pgsql  there doesn’t seem to be a subroutine/goto concept,  so I am
> trying to make the statements as concise as possible utilize composite types
> for the log table
>
>
>
>
>
> -- template
>
> -- header
>
> -- blah
>
> -- change log
>
> -- blah
>
> -- declare
>
>     Logsp type_log_site_process%ROWTYPE;
>
> BEGIN
>
> -- function setup
>
>     logsp.proc_id    :=0;
>
>     logsp.proc_name  :=vSpName;
>
>     logsp.step_id    :=1;
>
>     logsp.step_desc  :='';
>
>     Logsp.step_starttime := clock_timestamp();
>
>     Logsp.step_endtime   := clock_timestamp();
>
>     logsp.step_returncode :='';
>
>     logsp.activity_count :=1;
>
>     logsp.status_desc    :='';
>
>     logsp.status_flag    :='P';
>
>
>
> -- step block
>
>
>
> -- setup
>
>     Starttime = clock_timestamp();
>
>
>
> -- execute sql
>
> -- exception block
>
>    End time = clock_timestamp();
>
>
>
> -- log statement
>
>
>
> Instead of
>
> INSERT INTO log_site_process(
>
>     id, proc_id, proc_name, step_id, step_desc, step_starttime,
> step_endtime,
>
> step_returncode, activity_count, status_flag, status_desc)
>
>     VALUES (logsp.proc_id
>
>    , logsp.proc_name
>
>    , logsp.step_id
>
>    , logsp.step_desc
>
>    , Logsp.step_starttime
>
>    , Logsp.step_endtime
>
>    , logsp.step_returncode
>
>    , logsp.activity_count
>
>    , logsp.status_desc
>
>    , logsp.status_flag
>
> ;
>
> I’d like
>
> insert into log_site_process select * from (Logsp);   -- or values (logsp)
>
>
>
> but I can’t seem to get it to work.
>
>
>
> ERROR:  syntax error at or near "$1"
>
> LINE 1: ...ert into oww_mart_tbls.log_site_process select * from ( $1 )
>
>    ^
>
> Can someone let me know if I can do this and what the syntax is.

couple of things going on here:

first, the way to do insert from composite type is like this:

insert into foo select (f).*;

if f is type of foo.  The actual error you're getting is probably
confusion between variable names and literal objects.  Especially in
older postgres try not to have variable names that happen to be the
same name as tables or columns. I can't tell for sure since the the
whole function isn't posted.

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] Problem with pg_upgrade 8.3 to 9.1.4 - clog missing?!

[hubert depesz lubaczewski]

On Wed, Jun 13, 2012 at 02:55:41PM -0400, Bruce Momjian wrote:
> On Wed, Jun 13, 2012 at 08:43:23PM +0200, hubert depesz lubaczewski wrote:
> > On Wed, Jun 13, 2012 at 02:32:21PM -0400, Bruce Momjian wrote:
> > > I assume 8.3 vacuumdb did not generate these errors.  If it was 8.4 I
> > > would suggest it was because we don't copy visibility map files from
> > > pre-9.1 because those were not crash-safe, but 8.3 didn't have
> > > visibility map files (added in PG 8.4), so 8.3 should have been checking
> > > the exact same transaction ids as 9.1.
> > > 
> > > Basically, I can't think of a cause.
> > 
> > yeah, neither can we. but - any other test - even using the same source
> > of $PGDATA went flawless. I'm tempted to assume it's just a random
> > glitch in matrix, and just go on.
> 
> Is the vacuumdb still repeatedly failing?

well - in this particular upgraded database - i think so. I did run it
couple of times, it failed all the times.
But this $PGDATA is not longer used - we did another tests, and no other
test showed problems - i.e. vacuum worked fine.

Best regards,

depesz


-- 
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] Problem with pg_upgrade 8.3 to 9.1.4 - clog missing?!

[Bruce Momjian]

On Wed, Jun 13, 2012 at 08:43:23PM +0200, hubert depesz lubaczewski wrote:
> On Wed, Jun 13, 2012 at 02:32:21PM -0400, Bruce Momjian wrote:
> > I assume 8.3 vacuumdb did not generate these errors.  If it was 8.4 I
> > would suggest it was because we don't copy visibility map files from
> > pre-9.1 because those were not crash-safe, but 8.3 didn't have
> > visibility map files (added in PG 8.4), so 8.3 should have been checking
> > the exact same transaction ids as 9.1.
> > 
> > Basically, I can't think of a cause.
> 
> yeah, neither can we. but - any other test - even using the same source
> of $PGDATA went flawless. I'm tempted to assume it's just a random
> glitch in matrix, and just go on.

Is the vacuumdb still repeatedly failing?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] Problem with pg_upgrade 8.3 to 9.1.4 - clog missing?!

[hubert depesz lubaczewski]

On Wed, Jun 13, 2012 at 02:32:21PM -0400, Bruce Momjian wrote:
> I assume 8.3 vacuumdb did not generate these errors.  If it was 8.4 I
> would suggest it was because we don't copy visibility map files from
> pre-9.1 because those were not crash-safe, but 8.3 didn't have
> visibility map files (added in PG 8.4), so 8.3 should have been checking
> the exact same transaction ids as 9.1.
> 
> Basically, I can't think of a cause.

yeah, neither can we. but - any other test - even using the same source
of $PGDATA went flawless. I'm tempted to assume it's just a random
glitch in matrix, and just go on.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] Problem with pg_upgrade 8.3 to 9.1.4 - clog missing?!

[Bruce Momjian]

On Thu, Jun 07, 2012 at 09:16:49PM +0200, hubert depesz lubaczewski wrote:
> hi
> I just upgraded test copy of database of our customer (~ 600GB of data).
> upgrade went fine, no errors. but vacuumdb -azv ended with an error:
> 
> => vacuumdb --all --analyze -p 6665
> vacuumdb: vacuuming database "client_db"
> vacuumdb: vacuuming database "pg_audit"
> vacuumdb: vacuuming database "postgres"
> vacuumdb: vacuuming of database "postgres" failed: ERROR:  could not access 
> status of transaction 860626316
> DETAIL:  Could not open file "pg_clog/0334": No such file or directory.
> 
> I know we had these kind of errors before, but I thought they all got fixed.
> 
> What can I do to debug it more?
> 
> 8.3 was 8.3.16.
> 8.3.16 is from rpms, 9.1.4 is from source compilation.
> 
> both have integer datatimes, and both are for 64bit linux.

I assume 8.3 vacuumdb did not generate these errors.  If it was 8.4 I
would suggest it was because we don't copy visibility map files from
pre-9.1 because those were not crash-safe, but 8.3 didn't have
visibility map files (added in PG 8.4), so 8.3 should have been checking
the exact same transaction ids as 9.1.

Basically, I can't think of a cause.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] Create view is not accepting the parameter in postgres functions

[Merlin Moncure]

On Wed, Jun 13, 2012 at 12:25 PM, Tom Lane  wrote:
> Merlin Moncure  writes:
>> The CREATE VIEW statement does not allow parameterized arguments
>> apparently.
>
> Well, no.  What would it mean?  The view is likely to outlast the
> existence of the function argument.

right -- it's quite sensible even if superficially unusual: for
vanilla queries there is a very clear line between what can be
parameterized and what can't. CREATE VIEW looks like a query but does
something completely different and so obeys a different set of rules.

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] Create view is not accepting the parameter in postgres functions

[Tom Lane]

Merlin Moncure  writes:
> The CREATE VIEW statement does not allow parameterized arguments
> apparently.

Well, no.  What would it mean?  The view is likely to outlast the
existence of the function argument.

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

[GENERAL] composite type use in pl/gpsql

[Little, Douglas]

Hello,
Thanks in advance for taking my question.
Running on Greenplum 4.1.2/ based on PG 8.2.3 kernal

We make extensive use of functions to do our ETL.
So, I'm building a stored procedure template for our developers.
I'd like the template to log the sql statements to a logging table for 
audit/debug purposes.  Statements are logged after execution so we have a 
completion code.
I'd also like to have an exception block for each statement - so prior steps 
get committed.

Since out procedures can have a large number of steps  <20,  I'd like to avoid 
repeating the exception and logging code.
In PL/pgsql  there doesn't seem to be a subroutine/goto concept,  so I am 
trying to make the statements as concise as possible utilize composite types 
for the log table


-- template
-- header
-- blah
-- change log
-- blah
-- declare
Logsp type_log_site_process%ROWTYPE;
BEGIN
-- function setup
logsp.proc_id:=0;
logsp.proc_name  :=vSpName;
logsp.step_id:=1;
logsp.step_desc  :='';
Logsp.step_starttime := clock_timestamp();
Logsp.step_endtime   := clock_timestamp();
logsp.step_returncode :='';
logsp.activity_count :=1;
logsp.status_desc:='';
logsp.status_flag:='P';

-- step block

-- setup
Starttime = clock_timestamp();

-- execute sql
-- exception block
   End time = clock_timestamp();

-- log statement

Instead of
INSERT INTO log_site_process(
id, proc_id, proc_name, step_id, step_desc, step_starttime, 
step_endtime,
step_returncode, activity_count, status_flag, status_desc)
VALUES (logsp.proc_id
   , logsp.proc_name
   , logsp.step_id
   , logsp.step_desc
   , Logsp.step_starttime
   , Logsp.step_endtime
   , logsp.step_returncode
   , logsp.activity_count
   , logsp.status_desc
   , logsp.status_flag
;
I'd like
insert into log_site_process select * from (Logsp);   -- or values (logsp)

but I can't seem to get it to work.

ERROR:  syntax error at or near "$1"
LINE 1: ...ert into oww_mart_tbls.log_site_process select * from ( $1 )
   ^
Can someone let me know if I can do this and what the syntax is.


Thanks
Doug


Doug Little

Re: [GENERAL] Create view is not accepting the parameter in postgres functions

[Merlin Moncure]

On Wed, Jun 13, 2012 at 12:31 AM, Divyaprakash Y
 wrote:
>
> Hi,
>
>
>
> Is the following postgres function correct?
>
>
>
> CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)
>
>     RETURNS SETOF "B" AS
>
> $BODY$
>
>     CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = $1;
>
>     SELECT * FROM "B";
>
> $BODY$
>
>   LANGUAGE 'sql' VOLATILE
>
>   COST 100;
>
> ALTER FUNCTION "MyFun"(INTEGER) OWNER TO postgres;
>
>
>
> Where “B” is a table in the DB schema.
>
>
>
>
>
> Executing “select * from "MyFun"(1) “ throws the following error:
>
>
>
> ERROR:  there is no parameter $1
>
> LINE 2: ...W "A” AS SELECT * FROM "B" WHERE "Id" = $1;
>
>
>
> 
>
>
>
> Where as the following function works fine:
>
>
>
> CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)
>
>     RETURNS SETOF "B" AS
>
> $BODY$
>
>     CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = 1;
>
>     SELECT * FROM "B";
>
> $BODY$
>
>   LANGUAGE 'sql' VOLATILE
>
>   COST 100;
>
> ALTER FUNCTION "MyFun"(INTEGER) OWNER TO postgres;
>
>
>
> Where “Id” is hardcoded within the function.
>
>
>
> Is this expected? Please reply.

(this has absolutely nothing to do with named parameters)

The CREATE VIEW statement does not allow parameterized arguments
apparently.  If you want to do this, you have to switch to plpgsql and
use EXECUTE.

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] How to create c language in postgresql database. Thanks.

[leaf_yxj]

Hi Chris and Guys,
 
Thanks for your answers. I really appreciate it. Although I don't understand 
the whole things you guys mentioned to me. I think maybe I should do it by 
myself. I need to do a test. If there is any good guide/white paper, please 
give me a link for me to study.
 
Thanks.
 
Regards.
 
Grace




At 2012-06-13 15:29:21,"Chris Travers-5 [via PostgreSQL]" 
 wrote:
On Wed, Jun 13, 2012 at 12:19 AM, Craig Ringer <[hidden email]> wrote:

> On 06/13/2012 12:45 PM, Chris Travers wrote:
>>
>> On Tue, Jun 12, 2012 at 11:47 AM, John R Pierce <[hidden email]>
>> wrote:
>>>
>>> On 06/12/12 11:25 AM, leaf_yxj wrote:

 Thanks. You guys are right. I check the database. The C programm is
 there.
   - but why our application team keep ask me to give them the
 superuser
 privileges to create the C function. Should they use the superuser to
 create
 the C function. if yes , why they need it?
>>>
>>>
>>> yes, only a sql superuser can define a C function, as these have total
>>> access to crashing postgres's innards.
>>>
>> Not just the innards, but the file system (could be used to overwrite
>> data files), arbitrary system commands, etc..
>
> Hopefully not arbitrary system commands, in that I really hope nobody's nuts
> enough to run PostgreSQL as root or with write access to its own binaries.
> The data files are fair game, though, and replacement/modification of
> commands is probably possible in weaker installations.

Maybe not as arbitrary as it would as root, but at least arbitrary in
the sense of "able to do or access anything that the system will let
the Postgres process access."  That means all binaries an ordinary
user can access and all system calls that don't require root unless
you lock things down using something like SELinux.

Best Wishes,
Chris Travers

--
Sent via pgsql-general mailing list ([hidden email])
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/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712359.html
To unsubscribe from How to create c language in postgresql database. Thanks., 
click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-c-language-in-postgresql-database-Thanks-tp5712221p5712447.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Create view is not accepting the parameter in postgres functions

[Misa Simic]

Woops,

I thought:

CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)
RETURNS SETOF "B" AS
$BODY$
SELECT * FROM "B" WHERE "Id" = $1;
$BODY$
  LANGUAGE 'sql' STABLE
  COST 100;

2012/6/13 Misa Simic 

> I think temp table, would be better option if you must decide from some
> reason...
>
> However, why would you use View or temp table in that scenario? I mean
> what would be wrong with:
>
> CREATE OR REPLACE FUNCTION "MyFun"(INTEGER)
> RETURNS SETOF "B" AS
> $BODY$
> SELECT * FROM "B";
> $BODY$
>   LANGUAGE 'sql' STABLE
>   COST 100;
>
> Kind Regards,
>
> Misa
>
> 2012/6/13 Divyaprakash Y 
>
>> Hey,
>>
>> That works. Thanks for all the replies.
>>
>> The answer for your questions
>> 1. I am using Postgres 8.4.
>> 2. That was the snippet which I was using for the further processing in
>> my function.
>>
>> Also, few questions are as follows:
>> 1. How different the positional parameter is from the named parameter?
>> 2. I am able to use positional parameters in sql functions as in
>> PL/PgSQL function. This is the only case [create view] in which I could
>> not succeed.
>> 3. Which would be faster..temp table or view?
>>
>>
>> -Original Message-
>> From: Craig Ringer [mailto:ring...@ringerc.id.au]
>> Sent: Wednesday, June 13, 2012 12:39 PM
>> To: Divyaprakash Y
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Create view is not accepting the parameter in
>> postgres functions
>>
>> On 06/13/2012 01:31 PM, Divyaprakash Y wrote:
>>
>> > CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)
>> > RETURNS SETOF "B" AS
>> > $BODY$
>> > CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = $1;
>> > SELECT * FROM "B";
>> 
>> > Executing "select * from "MyFun"(1) " throws the following error:
>> >
>> > ERROR:  there is no parameter $1
>> > LINE 2: ...W "A" AS SELECT * FROM "B" WHERE "Id" = $1;
>>
>> Heh, that's an interesting one. What version of PostgreSQL are you
>> using?
>>
>> What exactly are you trying to accomplish with this? What problem are
>> you trying to solve?
>>
>>
>> On 9.1.3 I'm getting:
>>
>> CREATE OR REPLACE FUNCTION test(integer) RETURNS setof "A" AS $$
>> CREATE VIEW "B" AS SELECT * FROM "A" WHERE id = $1;
>> SELECT * FROM "B";
>> $$ LANGUAGE 'sql';
>>
>> ERROR:  relation "B" does not exist
>> LINE 3: SELECT * FROM "B";
>>
>> ... where "A" of course exists.
>>
>> I would not expect this to work, because AFAIK sql functions are
>> prepared and have their plans saved either when first run or when
>> created, one of the two. What you'll need is a PL/PgSQL function that
>> uses the 'EXECUTE' statement to create the view dynamically, eg:
>>
>> CREATE OR REPLACE FUNCTION test(integer) RETURNS SETOF "A" AS $$
>> BEGIN
>> EXECUTE 'CREATE VIEW "B" AS SELECT * FROM "A" WHERE id =
>> '||quote_literal($1);
>> RETURN QUERY SELECT * FROM "B";
>> END;
>> $$ LANGUAGE 'plpgsql';
>>
>> Note that this will fail when run a second time. You will have to DROP
>> the view, and you will be subject to all sorts of exciting cross-session
>>
>> race conditions. You can use CREATE TEMPORARY VIEW, but that'll still
>> exist until the session ends. Both approaches are slow.
>>
>> That leads back to my first question: What exactly are you trying to
>> accomplish with this? What problem are you trying to solve?
>>
>> --
>> Craig Ringer
>>
>>
>> __
>>  DISCLAIMER: This electronic message and any attachments to this
>> electronic
>>  message is intended for the exclusive use of the addressee(s) named
>> herein
>>  and may contain legally privileged and confidential information. It is
>> the
>>  property of Celstream Technologies Pvt Limited. If you are not the
>> intended
>>  recipient, you are hereby strictly notified not to copy, forward,
>> distribute
>>  or use this message or any attachments thereto. If you have received this
>>  message in error, please delete it and all copies thereof, from your
>> system
>>  and notify the sender at Celstream Technologies or
>>  administra...@celstream.com immediately.
>>
>> __
>>
>> --
>> 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] Create view is not accepting the parameter in postgres functions

[Misa Simic]

I think temp table, would be better option if you must decide from some
reason...

However, why would you use View or temp table in that scenario? I mean what
would be wrong with:

CREATE OR REPLACE FUNCTION "MyFun"(INTEGER)
RETURNS SETOF "B" AS
$BODY$
SELECT * FROM "B";
$BODY$
  LANGUAGE 'sql' STABLE
  COST 100;

Kind Regards,

Misa

2012/6/13 Divyaprakash Y 

> Hey,
>
> That works. Thanks for all the replies.
>
> The answer for your questions
> 1. I am using Postgres 8.4.
> 2. That was the snippet which I was using for the further processing in
> my function.
>
> Also, few questions are as follows:
> 1. How different the positional parameter is from the named parameter?
> 2. I am able to use positional parameters in sql functions as in
> PL/PgSQL function. This is the only case [create view] in which I could
> not succeed.
> 3. Which would be faster..temp table or view?
>
>
> -Original Message-
> From: Craig Ringer [mailto:ring...@ringerc.id.au]
> Sent: Wednesday, June 13, 2012 12:39 PM
> To: Divyaprakash Y
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Create view is not accepting the parameter in
> postgres functions
>
> On 06/13/2012 01:31 PM, Divyaprakash Y wrote:
>
> > CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)
> > RETURNS SETOF "B" AS
> > $BODY$
> > CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = $1;
> > SELECT * FROM "B";
> 
> > Executing "select * from "MyFun"(1) " throws the following error:
> >
> > ERROR:  there is no parameter $1
> > LINE 2: ...W "A" AS SELECT * FROM "B" WHERE "Id" = $1;
>
> Heh, that's an interesting one. What version of PostgreSQL are you
> using?
>
> What exactly are you trying to accomplish with this? What problem are
> you trying to solve?
>
>
> On 9.1.3 I'm getting:
>
> CREATE OR REPLACE FUNCTION test(integer) RETURNS setof "A" AS $$
> CREATE VIEW "B" AS SELECT * FROM "A" WHERE id = $1;
> SELECT * FROM "B";
> $$ LANGUAGE 'sql';
>
> ERROR:  relation "B" does not exist
> LINE 3: SELECT * FROM "B";
>
> ... where "A" of course exists.
>
> I would not expect this to work, because AFAIK sql functions are
> prepared and have their plans saved either when first run or when
> created, one of the two. What you'll need is a PL/PgSQL function that
> uses the 'EXECUTE' statement to create the view dynamically, eg:
>
> CREATE OR REPLACE FUNCTION test(integer) RETURNS SETOF "A" AS $$
> BEGIN
> EXECUTE 'CREATE VIEW "B" AS SELECT * FROM "A" WHERE id =
> '||quote_literal($1);
> RETURN QUERY SELECT * FROM "B";
> END;
> $$ LANGUAGE 'plpgsql';
>
> Note that this will fail when run a second time. You will have to DROP
> the view, and you will be subject to all sorts of exciting cross-session
>
> race conditions. You can use CREATE TEMPORARY VIEW, but that'll still
> exist until the session ends. Both approaches are slow.
>
> That leads back to my first question: What exactly are you trying to
> accomplish with this? What problem are you trying to solve?
>
> --
> Craig Ringer
>
>
> __
>  DISCLAIMER: This electronic message and any attachments to this electronic
>  message is intended for the exclusive use of the addressee(s) named herein
>  and may contain legally privileged and confidential information. It is the
>  property of Celstream Technologies Pvt Limited. If you are not the
> intended
>  recipient, you are hereby strictly notified not to copy, forward,
> distribute
>  or use this message or any attachments thereto. If you have received this
>  message in error, please delete it and all copies thereof, from your
> system
>  and notify the sender at Celstream Technologies or
>  administra...@celstream.com immediately.
>
> __
>
> --
> 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] Trying to execute several queries involving temp tables in a PHP script

[Misa Simic]

I agree with approach to have all in functions... In that case there would
not be a problem with temp tables because of inside 1 transaction they
would work...

suggestion was just to solve problem from php... what would be achiavable
just trough 1 query, or to use PDO and then:

$dbh->beginTransaction();
$dbh->exec(query1);
$dbh->exec(query2);
$dbh->exec(query3);
$dbh->commit();

Kind Regards,

Misa

2012/6/13 Leif Biberg Kristensen 

>  Onsdag 13. juni 2012 15.12.33 skrev Alexander Farber :
>
> > Any ideas please on how to handle this situation
> > in PHP scripts, do I really have to encapsulate
> > my calls into a pl/PgSQL function?
>
> I believe that Misa Simic's idea that you can do it all in a single query
> without temp tables is correct. But anyway, it's always a good idea to
> encapsulate multiple interdependent queries in a single pl/pgsql function.
> I
> tend to keep my PHP code as simple as possible, and do most of the work
> inside
> the database.
>
> regards, Leif
> http://code.google.com/p/yggdrasil-genealogy/
>
> --
> 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] Trying to execute several queries involving temp tables in a PHP script

[Leif Biberg Kristensen]

 Onsdag 13. juni 2012 15.12.33 skrev Alexander Farber :

> Any ideas please on how to handle this situation
> in PHP scripts, do I really have to encapsulate
> my calls into a pl/PgSQL function?

I believe that Misa Simic's idea that you can do it all in a single query 
without temp tables is correct. But anyway, it's always a good idea to 
encapsulate multiple interdependent queries in a single pl/pgsql function. I 
tend to keep my PHP code as simple as possible, and do most of the work inside 
the database.

regards, Leif
http://code.google.com/p/yggdrasil-genealogy/

-- 
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] Trying to execute several queries involving temp tables in a PHP script

[Misa Simic]

Hi Alexander,

I think you can have all in one query, without temp tables:

 SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM.
HH24:MI') as day,
   c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit,
   u.id, u.first_name, u.avatar, u.female, u.city,
u.vip > CURRENT_DATE as vip
   FROM pref_rounds r, pref_cards c, pref_users u
   WHERE u.id = c.id and
   r.rid = c.rid and
   r.rid in (

 select rid
   from pref_cards
   where stamp > now() - interval '1 day' and
   id in (
 select id
   from pref_money
   where yw = to_char(current_timestamp - interval '1
week', 'IYYY-IW')
   order by money
   desc limit 10

) and
   bid = 'Misere' and
   trix > 0


)
   order by rid, pos;



2012/6/13 Alexander Farber 

> Hello fello PostgreSQL users,
>
> with PHP 5.3.3 and PostgreSQL 8.4.11
> (and a pgbouncer, but I've tried without it too)
> I'm trying to execute several SQL queries
> with 2 temp tables (listed below) and then use
> the result of a final join to construct a JSON array.
>
> Unfortunately my script using prepare/execute
> (and I've tried query() too) fails with PHP error:
> "cannot insert multiple commands into a prepared statement".
>
> And when I split my statements into multiple
> prepare()/execute() or query() calls,
> then the temp. tables aren't found anymore.
>
> Any ideas please on how to handle this situation
> in PHP scripts, do I really have to encapsulate
> my calls into a pl/PgSQL function?
>
> More details on my query and setup:
>
> http://stackoverflow.com/questions/11010784/error-cannot-insert-multiple-commands-into-a-prepared-statement
>
> And below is my PHP code:
>
> try {
>$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
>$db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s',
>DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);
>
>$sth = $db->prepare("
>start transaction;
>create temporary table temp_ids (id varchar not null) on
> commit drop;
>insert into temp_ids (id)
>select id
>from pref_money
>where yw = to_char(current_timestamp - interval '1
> week', 'IYYY-IW')
>order by money
>desc limit 10;
>
>create temporary table temp_rids (rid integer not null) on
> commit drop;
>insert into temp_rids (rid)
>select rid
>from pref_cards
>where stamp > now() - interval '1 day' and
>id in (select id from temp_ids) and
>bid = 'Misere' and
>trix > 0;
>
>SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM.
> HH24:MI') as day,
>c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit,
>u.id, u.first_name, u.avatar, u.female, u.city,
> u.vip > CURRENT_DATE as vip
>FROM pref_rounds r, pref_cards c, pref_users u
>WHERE u.id = c.id and
>r.rid = c.rid and
>r.rid in (select rid from temp_rids)
>order by rid, pos;
>commit;
>");
>$sth->execute();
>while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
>  # construct a JSON array of objects
>}
> } catch (Exception $e) {
>exit('Database problem: ' . $e->getMessage());
> }
>
> Thank you
> Alex
>
> --
> 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] Trying to execute several queries involving temp tables in a PHP script

[Alban Hertroys]

On 13 June 2012 15:12, Alexander Farber  wrote:
> And when I split my statements into multiple
> prepare()/execute() or query() calls,
> then the temp. tables aren't found anymore.

Did you remember to wrap them in a transaction like you did in your
prepared statement?

-- 
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] PostgreSQL 9.2, SQL functions' named vs numbered parameters.

[Tom Lane]

Chris Travers  writes:
> In another thread it has been mentioned that SQL language functions in
> 9.2 will accept named parameters

This is correct.

> and that you can't mix named and
> numbered parameters.

This is not correct.  You can reference a parameter either by its name
or its number, same as in plpgsql.

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

[GENERAL] Daisy chaining replication slaves ?

[Rob Cowell]

Hi,

I'm a postgres newbie (in fact a DB newbie if I'm honest :))

I'm just wondering if there is a way to slave from a slave server?
I have a Postgres9.1.3 master serving up data quite happily to the web 
applications, and I have also set up a slave via streaming replication.
I've now been asked by my manager to set up a secondary slave, slaving off the 
first slave - e.g.

Master (DB1) --> Slave (DB2) --> Secondary_Slave (DB3)

Is there an easy way to accomplish this?
In my tests last night I setup the DB3 server to replicate from DB2, but for 
some reason it was complaining about 'max_wal_senders' being set to 1 on the 
master.
If all the settings are pointing DB3 at DB2, why would it be trying to talk to 
DB1 instead?

Cheers,
Rob.

[GENERAL] Trying to execute several queries involving temp tables in a PHP script

[Alexander Farber]

Hello fello PostgreSQL users,

with PHP 5.3.3 and PostgreSQL 8.4.11
(and a pgbouncer, but I've tried without it too)
I'm trying to execute several SQL queries
with 2 temp tables (listed below) and then use
the result of a final join to construct a JSON array.

Unfortunately my script using prepare/execute
(and I've tried query() too) fails with PHP error:
"cannot insert multiple commands into a prepared statement".

And when I split my statements into multiple
prepare()/execute() or query() calls,
then the temp. tables aren't found anymore.

Any ideas please on how to handle this situation
in PHP scripts, do I really have to encapsulate
my calls into a pl/PgSQL function?

More details on my query and setup:
http://stackoverflow.com/questions/11010784/error-cannot-insert-multiple-commands-into-a-prepared-statement

And below is my PHP code:

try {
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
$db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s',
DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);

$sth = $db->prepare("
start transaction;
create temporary table temp_ids (id varchar not null) on
commit drop;
insert into temp_ids (id)
select id
from pref_money
where yw = to_char(current_timestamp - interval '1
week', 'IYYY-IW')
order by money
desc limit 10;

create temporary table temp_rids (rid integer not null) on
commit drop;
insert into temp_rids (rid)
select rid
from pref_cards
where stamp > now() - interval '1 day' and
id in (select id from temp_ids) and
bid = 'Misere' and
trix > 0;

SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM.
HH24:MI') as day,
c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit,
u.id, u.first_name, u.avatar, u.female, u.city,
u.vip > CURRENT_DATE as vip
FROM pref_rounds r, pref_cards c, pref_users u
WHERE u.id = c.id and
r.rid = c.rid and
r.rid in (select rid from temp_rids)
order by rid, pos;
commit;
");
$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
  # construct a JSON array of objects
}
} catch (Exception $e) {
exit('Database problem: ' . $e->getMessage());
}

Thank you
Alex

-- 
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] Recovery continually requests new WAL files

[Alex Good]

On 13/06/12 11:10, Albe Laurenz wrote:

Alex Good wrote:

Although pgpool is involved this isn't actually about pgpool, I've

been

running through the recovery process manually to try and understand

what

needs to be done in order to get onlinve recovery working with pgpool.
Pgpool isn't actually running at the moment.

Oh, I see.


Anyway, I think what I had misunderstood was the meaning of the
'standby_mode' parameter in recovery.conf. If I remove that then the
process behaves as I expect it to except that the restoring server

ends

up restoring to a new timeline, I would prefer that it be on the same
timeline as the master, I have set recovery_target_timeline = 'latest'
in recovery.conf but this still increments the timeline. Is there any
way to get the recovery to stay on the same timeline other than
explicitly specifying the timeline?

That's why I asked if this is about streaming replication.

It is by design that a new timeline is opened after recovery.
This is to tell the WAL sequence from before and after recovery apart.
Is it a problem for you?

Yours,
Laurenz Albe
Well I had assumed that it was a bad thing as the way I am intending to 
use the recovery procedure is to add backup servers to the pgpool 
cluster and it seemed to make more sense that they all be on the same 
timeline.


Having thought about it though I don't think it matters, thanks very 
much for your help, I've been banging my head against this for a while.


Thanks
Alex Good

--
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] Recovery continually requests new WAL files

[Albe Laurenz]

Alex Good wrote:
> Although pgpool is involved this isn't actually about pgpool, I've
been
> running through the recovery process manually to try and understand
what
> needs to be done in order to get onlinve recovery working with pgpool.
> Pgpool isn't actually running at the moment.

Oh, I see.

> Anyway, I think what I had misunderstood was the meaning of the
> 'standby_mode' parameter in recovery.conf. If I remove that then the
> process behaves as I expect it to except that the restoring server
ends
> up restoring to a new timeline, I would prefer that it be on the same
> timeline as the master, I have set recovery_target_timeline = 'latest'
> in recovery.conf but this still increments the timeline. Is there any
> way to get the recovery to stay on the same timeline other than
> explicitly specifying the timeline?

That's why I asked if this is about streaming replication.

It is by design that a new timeline is opened after recovery.
This is to tell the WAL sequence from before and after recovery apart.
Is it a problem for you?

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] Recovery continually requests new WAL files

[Alex Good]

On 13/06/12 10:29, Albe Laurenz wrote:

Alex Good wrote:

What I expected to see was the server requesting each WAL file up

until

the one which was archived during pg_stop_backup and then the server
would consider itself to be recovered. Clearly I have misunderstood
something here.

These two servers are actually sat behind pgpool which is in

replication

mode (so I don't have streaming replication set up) which I chose
beccause it gives me synchronous replication as well as automatic
failover. I am trying to understand the recovery process so I can use

it

to set up pgpools' online recovery feature.

Oh, you didn't say that it is about pgpool.

You might try to ask their mailing lists:
http://www.pgpool.net/mediawiki/index.php/Mailing_lists

Yours,
Laurenz Albe
Although pgpool is involved this isn't actually about pgpool, I've been 
running through the recovery process manually to try and understand what 
needs to be done in order to get onlinve recovery working with pgpool. 
Pgpool isn't actually running at the moment.


Anyway, I think what I had misunderstood was the meaning of the 
'standby_mode' parameter in recovery.conf. If I remove that then the 
process behaves as I expect it to except that the restoring server ends 
up restoring to a new timeline, I would prefer that it be on the same 
timeline as the master, I have set recovery_target_timeline = 'latest' 
in recovery.conf but this still increments the timeline. Is there any 
way to get the recovery to stay on the same timeline other than 
explicitly specifying the timeline?


Thanks
Alex



--
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] Recovery continually requests new WAL files

[Albe Laurenz]

Alex Good wrote:
> What I expected to see was the server requesting each WAL file up
until
> the one which was archived during pg_stop_backup and then the server
> would consider itself to be recovered. Clearly I have misunderstood
> something here.
> 
> These two servers are actually sat behind pgpool which is in
replication
> mode (so I don't have streaming replication set up) which I chose
> beccause it gives me synchronous replication as well as automatic
> failover. I am trying to understand the recovery process so I can use
it
> to set up pgpools' online recovery feature.

Oh, you didn't say that it is about pgpool.

You might try to ask their mailing lists:
http://www.pgpool.net/mediawiki/index.php/Mailing_lists

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] PostgreSQL 9.2, SQL functions' named vs numbered parameters.

[Pavel Stehule]

2012/6/13 Chris Travers :
> Hi;
>
> In another thread it has been mentioned that SQL language functions in
> 9.2 will accept named parameters and that you can't mix named and
> numbered parameters.  Can anyone confirm this?   I am a bit concerned
> this will break a lot of LSMB stored procedures and that we won't be
> able to support 8.4-9.1 and 9.2+ with the same versions of the
> software.

you can mix named parameters and holders without problems:

postgres=# create or replace function fx(a int)
postgres-# returns int as $$ select $1; $$ language sql;
CREATE FUNCTION
postgres=# select fx(10);
 fx

 10
(1 row)

postgres=# select version();
 version

 PostgreSQL 9.2beta1 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.7.0 20120507 (Red Hat 4.7.
(1 row)

Regards

Pavel

>
> We use named parameters not only for function readability but also to
> provide information to the application as to what data the stored
> procedure expects.  You can see more at
> http://ledgersmbdev.blogspot.com/2011/10/introduction-to-soda.html
>
> Because our application tends to depend often on being able to look up
> the names of parameters, any time we pass parameters to an SQL
> language function these are named, but identified by number in the
> function body.  Consequently we have functions like:
>
>
> CREATE OR REPLACE FUNCTION asset_dep_get_usable_life_yr
> (in_usable_life numeric, in_start_date date, in_dep_date date)
> returns numeric as
> $$
>   SELECT CASE WHEN $3 IS NULL or get_fractional_year($2, $3) > $1
>               then $1
>               WHEN get_fractional_year($2, $3) < 0
>               THEN 0
>               ELSE get_fractional_year($2, $3)
>          END;
> $$ language sql;
>
> or
>
> CREATE OR REPLACE FUNCTION asset_item__add_note(in_id int, in_subject
> text, in_note text)
> RETURNS asset_note AS
> $$
> INSERT INTO asset_note (ref_key, subject, note) values ($1, $2, $3);
> SELECT * FROM asset_note WHERE id = currval('note_id_seq');
> $$ language sql;
>
>
>
> We do this a lot.  I think we have about 122 SQL language functions,
> and the only way to get this to work will be to work across versions
> if this restriction is there will be to port every one to PL/PGSQL,
> and I am not sure where/when we may have performance problems from
> that.
>
> Ideally there would be some way to have backwards-compatiblity here,
> but if that's not likely or an option, it would be helpful for me to
> get some clarification for that now so that I can document the problem
> and warn users.  We'd probably also require 9.2 sooner rather than
> later.
>
> 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

-- 
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] Recovery continually requests new WAL files

[Albe Laurenz]

Alex Good wrote:
> I have a simple setup with one master and one backup server. I have an
> issue where I have performed a backup and copied it to the data
> directory for the slave, written a recovery.conf and copied in the
> backup_label file and then started the server, it happily restores
> everything up until and including the WAL file mentioned in the
> backup_label and then attempts to obtain the next archive file which
has
> not yet been archived. I can't for the life of me figure out what is
> going on.

What else would you expect?

Are you planning to use streaming replication?

If yes, what are your configuration parameters for replication?

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

[GENERAL] PostgreSQL 9.2, SQL functions' named vs numbered parameters.

[Chris Travers]

Hi;

In another thread it has been mentioned that SQL language functions in
9.2 will accept named parameters and that you can't mix named and
numbered parameters.  Can anyone confirm this?   I am a bit concerned
this will break a lot of LSMB stored procedures and that we won't be
able to support 8.4-9.1 and 9.2+ with the same versions of the
software.

We use named parameters not only for function readability but also to
provide information to the application as to what data the stored
procedure expects.  You can see more at
http://ledgersmbdev.blogspot.com/2011/10/introduction-to-soda.html

Because our application tends to depend often on being able to look up
the names of parameters, any time we pass parameters to an SQL
language function these are named, but identified by number in the
function body.  Consequently we have functions like:


CREATE OR REPLACE FUNCTION asset_dep_get_usable_life_yr
(in_usable_life numeric, in_start_date date, in_dep_date date)
returns numeric as
$$
   SELECT CASE WHEN $3 IS NULL or get_fractional_year($2, $3) > $1
   then $1
   WHEN get_fractional_year($2, $3) < 0
   THEN 0
   ELSE get_fractional_year($2, $3)
  END;
$$ language sql;

or

CREATE OR REPLACE FUNCTION asset_item__add_note(in_id int, in_subject
text, in_note text)
RETURNS asset_note AS
$$
INSERT INTO asset_note (ref_key, subject, note) values ($1, $2, $3);
SELECT * FROM asset_note WHERE id = currval('note_id_seq');
$$ language sql;



We do this a lot.  I think we have about 122 SQL language functions,
and the only way to get this to work will be to work across versions
if this restriction is there will be to port every one to PL/PGSQL,
and I am not sure where/when we may have performance problems from
that.

Ideally there would be some way to have backwards-compatiblity here,
but if that's not likely or an option, it would be helpful for me to
get some clarification for that now so that I can document the problem
and warn users.  We'd probably also require 9.2 sooner rather than
later.

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] Create view is not accepting the parameter in postgres functions

[Divyaprakash Y]

Hey,

That works. Thanks for all the replies. 

The answer for your questions
1. I am using Postgres 8.4.
2. That was the snippet which I was using for the further processing in
my function.

Also, few questions are as follows:
1. How different the positional parameter is from the named parameter?
2. I am able to use positional parameters in sql functions as in
PL/PgSQL function. This is the only case [create view] in which I could
not succeed. 
3. Which would be faster..temp table or view?


-Original Message-
From: Craig Ringer [mailto:ring...@ringerc.id.au] 
Sent: Wednesday, June 13, 2012 12:39 PM
To: Divyaprakash Y
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Create view is not accepting the parameter in
postgres functions

On 06/13/2012 01:31 PM, Divyaprakash Y wrote:

> CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)
> RETURNS SETOF "B" AS
> $BODY$
> CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = $1;
> SELECT * FROM "B";

> Executing "select * from "MyFun"(1) " throws the following error:
>
> ERROR:  there is no parameter $1
> LINE 2: ...W "A" AS SELECT * FROM "B" WHERE "Id" = $1;

Heh, that's an interesting one. What version of PostgreSQL are you
using?

What exactly are you trying to accomplish with this? What problem are 
you trying to solve?


On 9.1.3 I'm getting:

CREATE OR REPLACE FUNCTION test(integer) RETURNS setof "A" AS $$
CREATE VIEW "B" AS SELECT * FROM "A" WHERE id = $1;
SELECT * FROM "B";
$$ LANGUAGE 'sql';

ERROR:  relation "B" does not exist
LINE 3: SELECT * FROM "B";

... where "A" of course exists.

I would not expect this to work, because AFAIK sql functions are 
prepared and have their plans saved either when first run or when 
created, one of the two. What you'll need is a PL/PgSQL function that 
uses the 'EXECUTE' statement to create the view dynamically, eg:

CREATE OR REPLACE FUNCTION test(integer) RETURNS SETOF "A" AS $$
BEGIN
 EXECUTE 'CREATE VIEW "B" AS SELECT * FROM "A" WHERE id = 
'||quote_literal($1);
 RETURN QUERY SELECT * FROM "B";
END;
$$ LANGUAGE 'plpgsql';

Note that this will fail when run a second time. You will have to DROP 
the view, and you will be subject to all sorts of exciting cross-session

race conditions. You can use CREATE TEMPORARY VIEW, but that'll still 
exist until the session ends. Both approaches are slow.

That leads back to my first question: What exactly are you trying to 
accomplish with this? What problem are you trying to solve?

--
Craig Ringer

__
 DISCLAIMER: This electronic message and any attachments to this electronic
 message is intended for the exclusive use of the addressee(s) named herein
 and may contain legally privileged and confidential information. It is the 
 property of Celstream Technologies Pvt Limited. If you are not the intended
 recipient, you are hereby strictly notified not to copy, forward, distribute
 or use this message or any attachments thereto. If you have received this
 message in error, please delete it and all copies thereof, from your system
 and notify the sender at Celstream Technologies or 
 administra...@celstream.com immediately.
__

-- 
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] Create view is not accepting the parameter in postgres functions

[Thomas Kellerer]

Chris Travers, 13.06.2012 09:16:

If this ever changes, I would certainly hope that the SQL language
functions would first be given named argument support.


This is coming in 9.2






--
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 create c language in postgresql database. Thanks.

[Chris Travers]

On Wed, Jun 13, 2012 at 12:19 AM, Craig Ringer  wrote:
> On 06/13/2012 12:45 PM, Chris Travers wrote:
>>
>> On Tue, Jun 12, 2012 at 11:47 AM, John R Pierce 
>> wrote:
>>>
>>> On 06/12/12 11:25 AM, leaf_yxj wrote:

 Thanks. You guys are right. I check the database. The C programm is
 there.
   - but why our application team keep ask me to give them the
 superuser
 privileges to create the C function. Should they use the superuser to
 create
 the C function. if yes , why they need it?
>>>
>>>
>>> yes, only a sql superuser can define a C function, as these have total
>>> access to crashing postgres's innards.
>>>
>> Not just the innards, but the file system (could be used to overwrite
>> data files), arbitrary system commands, etc..
>
> Hopefully not arbitrary system commands, in that I really hope nobody's nuts
> enough to run PostgreSQL as root or with write access to its own binaries.
> The data files are fair game, though, and replacement/modification of
> commands is probably possible in weaker installations.

Maybe not as arbitrary as it would as root, but at least arbitrary in
the sense of "able to do or access anything that the system will let
the Postgres process access."  That means all binaries an ordinary
user can access and all system calls that don't require root unless
you lock things down using something like SELinux.

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] How to create c language in postgresql database. Thanks.

[Craig Ringer]

On 06/13/2012 12:45 PM, Chris Travers wrote:

On Tue, Jun 12, 2012 at 11:47 AM, John R Pierce  wrote:

On 06/12/12 11:25 AM, leaf_yxj wrote:

Thanks. You guys are right. I check the database. The C programm is there.
   - but why our application team keep ask me to give them the superuser
privileges to create the C function. Should they use the superuser to create
the C function. if yes , why they need it?


yes, only a sql superuser can define a C function, as these have total
access to crashing postgres's innards.


Not just the innards, but the file system (could be used to overwrite
data files), arbitrary system commands, etc..
Hopefully not arbitrary system commands, in that I really hope nobody's 
nuts enough to run PostgreSQL as root or with write access to its own 
binaries. The data files are fair game, though, and 
replacement/modification of commands is probably possible in weaker 
installations.


--
Craig Ringer

--
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] Getting this error

[Craig Ringer]

On 06/13/2012 02:23 PM, yatler sahri wrote:

Hi

I'm running on a program


Source code?

PostgreSQL version you're using?

Database driver (libpq, PgJDBC, psqlODBC, etc) you're using, and its 
version?



Im getting the following error when running the program on postgress


Error log from PostgreSQL server logs?

Try enabling log_statement='all' in postgresql.conf .

--
Craig Ringer

Re: [GENERAL] Create view is not accepting the parameter in postgres functions

[Chris Travers]

On Wed, Jun 13, 2012 at 12:06 AM, Alban Hertroys  wrote:
> On 13 Jun 2012, at 7:31, Divyaprakash Y wrote:
>
>> Hi,
>>
>> Is the following postgres function correct?
>>
>> CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)
>
> Named parameters ^^^
>
>
>>                CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = $1;
>
>
> Positional parameters -^^
>


> You can't mix those. I don't think SQL functions support named parameters, so 
> using positional parameters throughout would be the solution.

Funny, we have been mixing in this way since at least Pg 8.1 with no
problems just because the names are of semantic value to the
application, and SQL language functions don't support named arguments.
 If this ever changes, I would certainly hope that the SQL language
functions would first be given named argument support.

You used to be able to mix directly in plpgsql iirc but I don't know
if that's still the case.

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] Create view is not accepting the parameter in postgres functions

[Craig Ringer]

On 06/13/2012 03:06 PM, Alban Hertroys wrote:

> Named parameters ^^^

Positional parameters -^^

You can't mix those. I don't think SQL functions support named parameters, so 
using positional parameters throughout would be the solution.


Yep, that's the immediate cause of failure, and I missed that in my 
reply so I'm answering the *next* question. Whoops, sorry.


If you remove the name and use positional style, the function still 
fails for a different reason, as per my post following.


--
Craig Ringer



--
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] Create view is not accepting the parameter in postgres functions

[Craig Ringer]

On 06/13/2012 01:31 PM, Divyaprakash Y wrote:


CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)
RETURNS SETOF "B" AS
$BODY$
CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = $1;
SELECT * FROM "B";



Executing “select * from "MyFun"(1) “ throws the following error:

ERROR:  there is no parameter $1
LINE 2: ...W "A” AS SELECT * FROM "B" WHERE "Id" = $1;


Heh, that's an interesting one. What version of PostgreSQL are you using?

What exactly are you trying to accomplish with this? What problem are 
you trying to solve?



On 9.1.3 I'm getting:

CREATE OR REPLACE FUNCTION test(integer) RETURNS setof "A" AS $$
CREATE VIEW "B" AS SELECT * FROM "A" WHERE id = $1;
SELECT * FROM "B";
$$ LANGUAGE 'sql';

ERROR:  relation "B" does not exist
LINE 3: SELECT * FROM "B";

... where "A" of course exists.

I would not expect this to work, because AFAIK sql functions are 
prepared and have their plans saved either when first run or when 
created, one of the two. What you'll need is a PL/PgSQL function that 
uses the 'EXECUTE' statement to create the view dynamically, eg:


CREATE OR REPLACE FUNCTION test(integer) RETURNS SETOF "A" AS $$
BEGIN
EXECUTE 'CREATE VIEW "B" AS SELECT * FROM "A" WHERE id = 
'||quote_literal($1);

RETURN QUERY SELECT * FROM "B";
END;
$$ LANGUAGE 'plpgsql';

Note that this will fail when run a second time. You will have to DROP 
the view, and you will be subject to all sorts of exciting cross-session 
race conditions. You can use CREATE TEMPORARY VIEW, but that'll still 
exist until the session ends. Both approaches are slow.


That leads back to my first question: What exactly are you trying to 
accomplish with this? What problem are you trying to solve?


--
Craig Ringer

--
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] Create view is not accepting the parameter in postgres functions

[Alban Hertroys]

On 13 Jun 2012, at 7:31, Divyaprakash Y wrote:

> Hi,
> 
> Is the following postgres function correct?
> 
> CREATE OR REPLACE FUNCTION "MyFun"("IdArgs" INTEGER)

Named parameters ^^^


>CREATE VIEW "A"  AS SELECT * FROM "B" WHERE "Id" = $1;


Positional parameters -^^

You can't mix those. I don't think SQL functions support named parameters, so 
using positional parameters throughout would be the solution.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find 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