For what its worth:
DO $$
DECLARE
vresult text;
begin
EXECUTE $qry$
WITH cols (c) AS ( VALUES ('col1'),('col2') )
SELECT string_agg('a.' || cols.c, ',')
FROM cols
WHERE 'foo' = $1
$qry$
USING 'foo'
INTO vresult;
RAISE NOTICE '%', vresult;
END;
$$;
I still haven't actually figured out what
1 - I added some new notes.
2 - That code I know and works fine. I used it in slightly different contexts
in other projects
Here it is a case .
A table with two columns , data type is irrelevant
I have a need in which I may get a.col1,a.col2 and other variations
Point is yes I could build a
Please don't top-post - it makes following the thread a lot harder.
On Mon, Nov 21, 2016 at 4:15 PM, Armand Pirvu (home) wrote:
> Played with unnest but not much luck
>
If you want help you will need to show your work - ideally with examples
that can execute with meaningful data on an empty da
Played with unnest but not much luck
NOTICE: {item_id,show_id}
NOTICE: item_id
It takes only the first array element in consideration
Ug
On Nov 21, 2016, at 5:02 PM, Armand Pirvu (home) wrote:
> My bad on the back tick. No idea why it turned that way
>
> OK got that David.
>
> Th
My bad on the back tick. No idea why it turned that way
OK got that David.
The idea is that I have the pk columns in an array which I would like to
manipulate from the array itself rather than running same query variations
multiple times
For example I get in foo
{item_id,show_id}
And from h
On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home) wrote:
> Hi
>
> Is there anyway I can pass a variable in the array_to_string function ?
>
>
Yes, just like you can pass variables to any other function...
> CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$
>
> DECLARE
> foo text;
>
Hi
Is there anyway I can pass a variable in the array_to_string function ?
CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$
DECLARE
foo text;
foo1 text;
begin
execute
'select ARRAY( SELECT d.COLUMN_NAME::text from
information_schema.constraint_table_usage c,
information_schema.key_co
On 01/21/2016 10:48 PM, Sachin Srivastava wrote:
Dear Adrian,
So, how the effective way to search this because I have around 1300 tables.
See Gilles response. From that I gather global variables are not
automatically transferred and it is up to you to decide where to put
them. Per your origi
Hi,
>
> On 01/20/2016 07:35 PM, Sachin Srivastava wrote:
>
> Dear Folks,
>
> I have a question about global variables in Oracle pl/sql package.
> Where
> are these variables when package is converted to schema from Oracle to
> Postgres through Ora2PG Tool?
>
Ora2Pg doesn't exp
Dear Adrian,
So, how the effective way to search this because I have around 1300 tables.
Regards,
SS
On Thu, Jan 21, 2016 at 8:48 PM, Adrian Klaver
wrote:
> On 01/20/2016 07:35 PM, Sachin Srivastava wrote:
>
>> Dear Folks,
>>
>> I have a question about global variables in Oracle pl/sql package
On 01/20/2016 07:35 PM, Sachin Srivastava wrote:
Dear Folks,
I have a question about global variables in Oracle pl/sql package. Where
are these variables when package is converted to schema from Oracle to
Postgres through Ora2PG Tool?
For example, package
Best guess it is stored in a table i
Dear Folks,
I have a question about global variables in Oracle pl/sql package. Where
are these variables when package is converted to schema from Oracle to
Postgres through Ora2PG Tool?
For example, package
Roger Niederland writes:
> I stripped enough out of the database that it is only good for a test
> case. Here is a public url for getting at the database backup:
I've committed a fix for this:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e4e60e7b6125e77f679861ebf43cc6b9f9db
Hello,
I stripped down the original query to what is below. I am not saying
that the query below
is useful except to show an error I am getting in Postgresql 9.1.1 on
both SL6.1 (64 bit) and
Windows 2008 server 9.1.1 (32-bit and 64-bit). The error I am getting is:
ERROR: variable not found
On 11/1/2011 8:30 PM, Tom Lane wrote:
Roger Niederland writes:
I extracted the tables from the database which generates the error above
I eliminated most of the columns such that this query still exhibits
this behavior to reduce
the file size. I have a zipped file from windows postgresql 9.1
Roger Niederland writes:
> I extracted the tables from the database which generates the error above
> I eliminated most of the columns such that this query still exhibits
> this behavior to reduce
> the file size. I have a zipped file from windows postgresql 9.1.1
> (32-bit) which is about 1.5
On 11/1/2011 3:54 PM, Roger Niederland wrote:
Hello,
I stripped down the original query to what is below. I am not saying
that the query below
is useful except to show an error I am getting in Postgresql 9.1.1 on
both SL6.1 (64 bit) and
Windows 2008 server 9.1.1 (32-bit and 64-bit). The e
Roger Niederland writes:
> I stripped down the original query to what is below. I am not saying
> that the query below
> is useful except to show an error I am getting in Postgresql 9.1.1 on
> both SL6.1 (64 bit) and
> Windows 2008 server 9.1.1 (32-bit and 64-bit). The error I am getting is:
Hello,
I stripped down the original query to what is below. I am not saying
that the query below
is useful except to show an error I am getting in Postgresql 9.1.1 on
both SL6.1 (64 bit) and
Windows 2008 server 9.1.1 (32-bit and 64-bit). The error I am getting is:
ERROR: variable not found
On Sep 2, 2011, at 2:31 PM, Bob Pawley wrote:
> It seems to work when I hard code the column name and array point, so I was
> hoping to make it work through a loop using variables for column and array
> point.
>
> Does this make sense??
Building queries this way is tedious & error prone; that'
-Original Message-
From: Bill Moran
Sent: Friday, September 02, 2011 10:53 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name
In response to "Bob Pawley" :
I am getting an error -- "column "1" does not exist"
Select
In response to "Bob Pawley" :
>
> I am getting an error -- "column "1" does not exist"
> Select "1" into column ;
Where are you selecting "1" from? This query has no FROM clause, so of
course the column doesn't exist.
The previous query, "SELECT 2 INTO point_array" is going to put the
i
-Original Message-
From: Raymond O'Donnell
Sent: Friday, September 02, 2011 10:38 AM
To: Bob Pawley
Cc: Bill Moran ; Postgresql
Subject: Re: [GENERAL] Variable column name
On 02/09/2011 18:33, Bob Pawley wrote:
-Original Message-
From: Bill Moran
Sent: Thu
On 02/09/2011 18:33, Bob Pawley wrote:
>
>
> -Original Message-
>> From: Bill Moran
> Sent: Thursday, September 01, 2011 8:19 AM
> To: Bob Pawley
> Cc: Postgresql
> Subject: Re: [GENERAL] Variable column name
>
> http://www.postgresql.org/docs/9
-Original Message-
From: Bill Moran
Sent: Thursday, September 01, 2011 8:19 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name
http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html
Section 39.5.4
If you're not familiar with plpgsql at all
In response to "Bob Pawley" :
>
> From: Bill Moran
>
> In response to "Bob Pawley" :
> >
> > I want to add information to multiple columns (20 - 40) by employing a
> > loop. Each pass of the loop will populate one column with an array.
> >
> > I have tried and I have read that variables can not
On Thursday, September 01, 2011 8:04:49 am Bob Pawley wrote:
> -Original Message-
> From: Bill Moran
> Sent: Thursday, September 01, 2011 7:59 AM
> To: Bob Pawley
> Cc: Postgresql
> Subject: Re: [GENERAL] Variable column name
>
> In response to "Bob Pawley&quo
On Sep 1, 2011, at 9:04 AM, Bob Pawley wrote:
> Would it be possible for you to point me to an example??
The EXECUTE command is what you want.
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list (pgsql-general@
-Original Message-
From: Bill Moran
Sent: Thursday, September 01, 2011 7:59 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name
In response to "Bob Pawley" :
I want to add information to multiple columns (20 – 40) by employing a
loop. Each pass o
In response to "Bob Pawley" :
>
> I want to add information to multiple columns (20 – 40) by employing a loop.
> Each pass of the loop will populate one column with an array.
>
> I have tried and I have read that variables can not be used to control column
> names.
>
> Is there a means of wor
Hi
I want to add information to multiple columns (20 – 40) by employing a loop.
Each pass of the loop will populate one column with an array.
I have tried and I have read that variables can not be used to control column
names.
Is there a means of working around this restriction other than cre
zhong ming wu wrote:
It seems that one cannot use the variable name that is the same as the
column name of a relation like in the following function
--
create or replace function bla() returns void language plpgsql as $$
declare
email varchar;
begin
select email into email fr
On Wednesday 08 September 2010 5:23:20 pm zhong ming wu wrote:
> It seems that one cannot use the variable name that is the same as the
> column name of a relation like in the following function
>
> --
> create or replace function bla() returns void language plpgsql as $$
> declare
> emai
It seems that one cannot use the variable name that is the same as the
column name of a relation like in the following function
--
create or replace function bla() returns void language plpgsql as $$
declare
email varchar;
begin
select email into email from pass where id=1;
Tom Lane wrote:
> Julian Mehnle writes:
> > Can anyone confirm that --variable command-line options are evaluated
> > before .psqlrc is read and executed? If so, does anyone know the
> > rationale for that? It seems counterintuitive to me, as it makes
> > overriding variables from the command-l
Julian Mehnle writes:
> Can anyone confirm that --variable command-line options are evaluated
> before .psqlrc is read and executed? If so, does anyone know the
> rationale for that? It seems counterintuitive to me, as it makes
> overriding variables from the command-line impossible.
Seems enti
Hi all,
I'm trying to add the database host name to my psql prompts. The obvious
solution is to add %M or %m to the PROMPT{1,2} variables in ~/.psqlrc.
However I have to work with a few databases that can be reached only
through SSH tunnels, for which I use aliases like this:
alias dbfoo='ssh
- glext...@gmail.com wrote:
> Hi--
>
>
> I'm having a bit of trouble with the EXECUTE statement for the
> following function.
>
>
> I saw a lot of traffic re. plpgsql & variable substitution for 8.4,
> but I'm convinced this is something simple (newbie):
>
>
>
> CREATE FUNCTION topm
Hi--
I'm having a bit of trouble with the EXECUTE statement for the
following function.
I saw a lot of traffic re. plpgsql & variable substitution for 8.4,
but I'm convinced this is something simple (newbie):
CREATE FUNCTION topmixtot (int[]) RETURNS SETOF record AS $$
DECLARE
gids ALIAS
Hi all,
I've written a simple trigger function to store the old row in another table to
log the data:
CREATE FUNCTION logusers() RETURNS trigger AS $$
BEGIN
INSERT INTO log.users SELECT FROM data.users WHERE id=OLD.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER
"Reg Me Please" <[EMAIL PROTECTED]> writes:
> Of course, in my opinion at least, there's no real reason for the above
> syntax limitation, as the sematics is not.
Is not what? Is not sensible?
> create or replace function f_limoff_1( l int, o int )
> returns setof atable as $$
> select * from at
"Tom Lane" <[EMAIL PROTECTED]> writes:
> Sam Mason <[EMAIL PROTECTED]> writes:
>> In relational algebra terms, try thinking about what would happen if you
>> did something like:
>
>> SELECT * FROM foo LIMIT val;
>
>> Where the table foo has more than one row (and val had different values
>> for
Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto:
> On 11/15/07, Reg Me Please <[EMAIL PROTECTED]> wrote:
> > In any case, what'd be the benefit for not allowing "variables" as LIMIT
> > and OFFSET argument?
>
> When you can fully describe the semantics of your example, you'll
> proba
On Fri, Nov 16, 2007 at 01:38:30AM -0500, Tom Lane wrote:
> Sam Mason <[EMAIL PROTECTED]> writes:
> > wow, that's kind of fun isn't it. I only thought you could put a
> > constant in there. Maybe I should have had a look in the grammar/tested
> > it first!
>
> IIRC, it used to be restricted to a
Reg Me Please wrote:
Il Thursday 15 November 2007 20:28:17 hai scritto:
Reg Me Please wrote:
In my opinion I would say it's more a problem with the syntax checker
that with the planner ("semantics" in my lingo). But I could be wrong.
Well, what it won't let you do is have a subquery in the LIM
Sam Mason <[EMAIL PROTECTED]> writes:
> wow, that's kind of fun isn't it. I only thought you could put a
> constant in there. Maybe I should have had a look in the grammar/tested
> it first!
IIRC, it used to be restricted to a constant, a few revisions back.
In current releases the only restrict
On 11/16/07, Reg Me Please <[EMAIL PROTECTED]> wrote:
> Il Friday 16 November 2007 08:33:14 Tom Lane ha scritto:
> > Let me try to explain one more time. You propose allowing
> >
> > select ... from
> > table1 join table2 on table1.x = table2.y
> > limit table1.z
> >
> > Now
Il Friday 16 November 2007 08:33:14 Tom Lane ha scritto:
> Reg Me Please <[EMAIL PROTECTED]> writes:
> >> The OP's complaint is that we don't allow a variable of the query's own
> >> level, but AFAICT he's still not grasped the point that that leads to an
> >> indeterminate limit value ...
> >
> >
Reg Me Please <[EMAIL PROTECTED]> writes:
>> The OP's complaint is that we don't allow a variable of the query's own
>> level, but AFAICT he's still not grasped the point that that leads to an
>> indeterminate limit value ...
> So it works, but it's not serious enough to be unlocked.
You really d
Sam Mason <[EMAIL PROTECTED]> writes:
> On Fri, Nov 16, 2007 at 01:38:30AM -0500, Tom Lane wrote:
>> IIRC, it used to be restricted to a constant, a few revisions back.
> I'm amazed it supports anything more than a constant. The values are
> almost always going to come from external code, so ther
Il Friday 16 November 2007 07:38:30 Tom Lane ha scritto:
> IIRC, it used to be restricted to a constant, a few revisions back.
> In current releases the only restriction that stems from laziness is
> not allowing a sub-select. (If anyone were to put forward a serious
> use-case, we'd probably go f
Il Thursday 15 November 2007 23:08:10 Richard Huxton ha scritto:
> Reg Me Please wrote:
> > Il Thursday 15 November 2007 20:28:17 hai scritto:
> >> Reg Me Please wrote:
> >>> In my opinion I would say it's more a problem with the syntax checker
> >>> that with the planner ("semantics" in my lingo).
On Thu, Nov 15, 2007 at 07:28:17PM +, Richard Huxton wrote:
> Reg Me Please wrote:
> >As Sam says I should be able to "put an IMMUTABLE expression into
> >a LIMIT or OFFSET". And under some circumstances (SQL function
> >body) it's true even with VARIABLE expressions like function call
> >argum
Reg Me Please wrote:
Sorry but I don't understand.
Either the LIMIT and OFFSET are to be definitely CONSTANT or not.
They must be constant during the execution of the query.
In the SQL function body the LIMIT and the OFFSET *are definitely not*
CONSTANT. And the planner can do its job at bes
Sorry but I don't understand.
Either the LIMIT and OFFSET are to be definitely CONSTANT or not.
In the SQL function body the LIMIT and the OFFSET *are definitely not*
CONSTANT. And the planner can do its job at best as usual.
As Sam says I should be able to "put an IMMUTABLE expression into a LI
Il Thursday 15 November 2007 20:28:17 hai scritto:
> Reg Me Please wrote:
> > Sorry but I don't understand.
> >
> > Either the LIMIT and OFFSET are to be definitely CONSTANT or not.
>
> They must be constant during the execution of the query.
>
> > In the SQL function body the LIMIT and the OFFSET
On Thu, Nov 15, 2007 at 06:08:20PM +0100, Reg Me Please wrote:
> Your remarks make a lot of sense. Of course.
good! it's interesting to see how things like this fit together.
> But then why allowing the LIMIT and the OFFSET as coming from function
> argument evaluations?
I believe the query is
Sam Mason <[EMAIL PROTECTED]> writes:
> In relational algebra terms, try thinking about what would happen if you
> did something like:
> SELECT * FROM foo LIMIT val;
> Where the table foo has more than one row (and val had different values
> for each row). Which row would the database use? I
On Thu, Nov 15, 2007 at 05:34:43PM +0100, Reg Me Please wrote:
> Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto:
> > On 11/15/07, Reg Me Please <[EMAIL PROTECTED]> wrote:
> > > In any case, what'd be the benefit for not allowing "variables" as LIMIT
> > > and OFFSET argument?
> >
>
Sam Mason <[EMAIL PROTECTED]> writes:
> On Thu, Nov 15, 2007 at 06:08:20PM +0100, Reg Me Please wrote:
>> But then why allowing the LIMIT and the OFFSET as coming from function
>> argument evaluations?
> I believe the query is planned by ignoring the LIMIT and OFFSET.
No, it still knows there wi
Il Thursday 15 November 2007 17:55:42 Sam Mason ha scritto:
> On Thu, Nov 15, 2007 at 05:34:43PM +0100, Reg Me Please wrote:
> > Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto:
> > > On 11/15/07, Reg Me Please <[EMAIL PROTECTED]> wrote:
> > > > In any case, what'd be the benefit for
In any case, what'd be the benefit for not allowing "variables" as LIMIT and
OFFSET argument?
--
Reg me Please
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joinin
On 11/15/07, Reg Me Please <[EMAIL PROTECTED]> wrote:
> In any case, what'd be the benefit for not allowing "variables" as LIMIT and
> OFFSET argument?
When you can fully describe the semantics of your example, you'll
probably be able to answer that question too :)
---(en
Hello
LIMIT has impact on execution plan, so there cannot be variables. Use
SRF function and dynamic statements instead.
Regards
Pavel
On 15/11/2007, Reg Me Please <[EMAIL PROTECTED]> wrote:
> In any case, what'd be the benefit for not allowing "variables" as LIMIT and
> OFFSET argument?
>
> --
Reg Me Please <[EMAIL PROTECTED]> writes:
> create table limoff( l int, o int );
> insert into limoff values ( 10,2 );
> select a.* from atable a,limoff limit l offset o;
I am truly curious what you think the semantics of that ought to be.
regards, tom lane
--
Hi all.
I'd need to implement a "parametric windowed select" over a table
called "atable". The idea is to have a one row table to maintain
the LIMIT and the OFFSET for the selects. If I try this:
create table limoff( l int, o int );
insert into limoff values ( 10,2 );
select a.* from atable a,lim
> A case for dynamic
> SQL!http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQ
> L-STATEMENTS-EXECUTING-DYN
>
> Yours,
> Laurenz Albe
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
P
Rob Shepherd wrote:
> I'm following the synatx
>
> COPY TO 'filename'
>
> .but i'm trying to use a variable as the filename.
>
[...]
>
> returns the error
>
> ERROR: syntax error at or near "$1" at character 21
> QUERY: COPY tt_archive TO $1
> CONTEXT: SQL statement in PL/PgSQL funct
Dear postgres-users,
I'm currently working on a stored procedure, but having an error which
seems odd.
I'm following the synatx
COPY TO 'filename'
.but i'm trying to use a variable as the filename.
1 : CREATE OR REPLACE FUNCTION archive() RETURNS void AS
2 : $BODY$DECLARE
3 : ts times
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Variable return type...
On Wed, Oct 26, 2005 at 06:14:23PM -0600, Cristian Prieto wrote:
> Hi, I was working in a set of SPs inside PL/pgSQL and I was wonder if I
> could return a set of variable types from a function, for example, i
On Wed, Oct 26, 2005 at 06:14:23PM -0600, Cristian Prieto wrote:
> Hi, I was working in a set of SPs inside PL/pgSQL and I was wonder if I
> could return a set of variable types from a function, for example, in some
> time the same function could return a set of tuples with an integer and a
> strin
Hi, I was working in a set of SPs inside PL/pgSQL and I was
wonder if I could return a set of variable types from a function, for example,
in some time the same function could return a set of tuples with an integer and
a string, in other times It may return a set of tuples with an integer,
Richard Huxton wrote:
George Woodring wrote:
Is possible to declare a variable to build the column name
Tableid varchar(20) := TG_RELNAME || ''id'';
And then use this variable to get the PK value?
Tableidvalue int4 := NEW.tableid;
No. You can build a dynamic query via EXECUTE, but that can't acces
George Woodring wrote:
Is possible to declare a variable to build the column name
Tableid varchar(20) := TG_RELNAME || ''id'';
And then use this variable to get the PK value?
Tableidvalue int4 := NEW.tableid;
No. You can build a dynamic query via EXECUTE, but that can't access
NEW/OLD.
--
Richa
I am trying to create a trigger that needs to capture the primary key
value out of NEW variable.
However the trigger can be called from 2 different tables whose PKs are
Table1id
Table2id
Is possible to declare a variable to build the column name
Tableid varchar(20) := TG_RELNAME || ''id'';
And
[EMAIL PROTECTED] writes:
> How can i create variable case data bases ?
Double quote the database name:
psql -c 'create database "MiXeD";'
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
Whenever i create a database with command :
psql -h HostName -p PortName -c "create database DATABASENAME "
template1
The database is created with small case .
How can i create variable case data bases ?
regards
AMBIKA
---(end of broa
Hello everybody,
I converted some existing plpgsql-scripts from 7.0.3 to 7.1 (CVS from
2001-01-09) and had problems with a variable RESULT in my script. E.g.:
drop function bla(integer);
create function bla(integer) returns integer as '
declare
RESULT integer;
begin
List of databases
Database | Owner
+--
Newnham| prlw1
% psql Newnham
psql: connection to database "newnham" failed - FATAL 1: Database "newnham" does not
exist in the system catalog.
template1=> \c 'Newnham'
FATAL 1: Database "newnham" does not exist in the
I'm trying to implement a sort of templates for a user database.
I tought of two possibilities. In the first I have a table like this:
CREATE TABLE templates
(
tplname varchar(32),
fieldname varchar(32),
value varchar(32)
);
Where I specify the value to be assigned to each
80 matches
Mail list logo
