2012/4/19 thomas veymont :
> that made it, thank you.
> For other readers, here is what I finally did :
>
> CREATE TABLE mytable (...)
>
> CREATE FUNCTION (...) RETURNS SETOF mytable AS $$
> DECLARE
> r mytable%rowtype
> BEGIN
> ...
> FOR r IN select * from mytable
> LOOP
>
>
that made it, thank you.
For other readers, here is what I finally did :
CREATE TABLE mytable (...)
CREATE FUNCTION (...) RETURNS SETOF mytable AS $$
DECLARE
r mytable%rowtype
BEGIN
...
FOR r IN select * from mytable
LOOP
RETURN next r;
END LOOP;
RETURN;
END
2012/4/19 thomas veymont :
> hi Pavel,
>
> thanks for your answer,
>
> I don't understand exactly how "y" should be declared, and how it
> should be returned by the function (as a table,
> as a "set of record", or maybe as some kind of generic object, I don't
> know exactly what's possible with pl
hi Pavel,
thanks for your answer,
I don't understand exactly how "y" should be declared, and how it
should be returned by the function (as a table,
as a "set of record", or maybe as some kind of generic object, I don't
know exactly what's possible with pl/psql.).
cheers
Tom
2012/4/18 Pavel Steh
Hello
please try:
postgres=# create or replace function foo()
returns void as $$
declare r x;
begin
for r in select * from x
loop
insert into y values(r.*);
end loop;
end;
$$ language plpgsql;
Regards
Pavel
2012/4/18 thomas veymont :
> (sorry my previous email was truncated)
>
> hi,
2011/11/29 Muiz :
> I write a function to execute a sql string. E.g. "update tableA set
> field1='abc' where name='123'; deletee from tableB where id=333;"
> The following is my function:
> ---
Steve Northamer writes:
> So my questions are: 1) How do we cause the paymentcalc function to be
> executed only once?
In recent versions, I think marking it volatile would be sufficient.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> So my questions are: 1) How do we cause the paymentcalc function to be
> executed only once? and 2) How do we call a table returning function with
> inputs from a table?
>
> Thank you very much!
>
> Steve
>
WITH func AS (
SELECT FUNC(...) AS func_result FROM ...
)
SELECT (func.func_re
the best way is to put all calls into one function and store values to
variables..
if that is not suitable you can try the way (which im not sure if anyone
uses) and it is to store values to sequences if value type is integer.
for other formats you will have to do conversions.
but im not sure if s
Hi,
Maybe calling a function from within another function would be a solution to
you.
Cheers,
WBL
On Tue, May 3, 2011 at 4:10 AM, Pavel Stehule wrote:
> Hello
>
> no, it's not possible
>
> Regards
>
> Pavel Stehule
>
> 2011/5/2 Charles N. Charotti :
> > Hello everybody !
> >
> > I want to know
Hello
no, it's not possible
Regards
Pavel Stehule
2011/5/2 Charles N. Charotti :
> Hello everybody !
>
> I want to know if I could share PLpgSQL variables between different
> functions and within different calls just using memory (not tables or other
> methods).
>
> If it is really possible ?
>
stupid me.
I edited a function with the same name, but different parameter types and
tested with the other function.
so everything works fine.
thanks everybody for help.
best regards,
Uwe
On 10 March 2011 11:53, Uwe Bartels wrote:
> same same.
> all errors including syntax_error match to othe
On Thu, Mar 10, 2011 at 2:53 AM, Uwe Bartels wrote:
> same same.
> all errors including syntax_error match to others, but I checked it again.
> and the exception remains.
>
I'm just guessing here, but is it throwing a new exception in the exception
handler? I realize that the exception that is
same same.
all errors including syntax_error match to others, but I checked it again.
and the exception remains.
Uwe
On 10 March 2011 10:56, Samuel Gendler wrote:
>
>
> On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels wrote:
>
>> Hi,
>>
>> Yes, of course is this sql producing an error.
>> The po
On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels wrote:
> Hi,
> Yes, of course is this sql producing an error.
> The point is, I want to trap the error and handle it. Here in this case I
> set the variable l_state and l_message.
>
Doh! Of course. Sorry about that. What happens when you explicit
yes, p_id is a variable in my code which is bigger. so ignore the update
statement.
Uwe
On 10 March 2011 01:20, bricklen wrote:
> On Wed, Mar 9, 2011 at 2:08 PM, Samuel Gendler
> wrote:
> > when I run 'select 1count(*) from table' in my postgres 8.4 installation,
> I
> > get the exact same err
Hi,
Yes, of course is this sql producing an error.
The point is, I want to trap the error and handle it. Here in this case I
set the variable l_state and l_message.
But
The function exits with an exception instead of returning. So the exception
statement does not work as I think i would.
And
On Wed, Mar 9, 2011 at 2:08 PM, Samuel Gendler
wrote:
> when I run 'select 1count(*) from table' in my postgres 8.4 installation, I
> get the exact same error message. Assuming the '1count()' function does
> exist, perhaps you need to full qualify it with a schema name?
> It looks to me like the
when I run 'select 1count(*) from table' in my postgres 8.4 installation, I
get the exact same error message. Assuming the '1count()' function does
exist, perhaps you need to full qualify it with a schema name?
It looks to me like the query you are passing to the procedure is invalid
and is gener
2010/8/19 Tom Lane :
>> 2010/8/18 Horváth Imre :
>>> It don't work...
>
> A function returning a scalar type cannot control the column name
> assigned to the scalar in the calling query. To do that, you need to
> return a composite type, which means there need to be at least two OUT
> parameters i
> 2010/8/18 Horváth Imre :
>> It don't work...
A function returning a scalar type cannot control the column name
assigned to the scalar in the calling query. To do that, you need to
return a composite type, which means there need to be at least two OUT
parameters if you're trying to do it via OUT
What is your PG version?
2010/8/18 Horváth Imre :
> It don't work...
>
> Only select into status * from... works.
>
> Imre Horvath
>
> 2010. 08. 18, szerda keltezéssel 09.42-kor Sergey Konoplev ezt írta:
>> Hi,
>>
>> SELECT column_name
>> INTO var_name
>> FROM ...
>>
>> 2010/8/17 Horváth Imre :
>>
It don't work...
Only select into status * from... works.
Imre Horvath
2010. 08. 18, szerda keltezéssel 09.42-kor Sergey Konoplev ezt írta:
> Hi,
>
> SELECT column_name
> INTO var_name
> FROM ...
>
> 2010/8/17 Horváth Imre :
> > Hi!
> >
> > My question is, how can I get the out
It don't work...
Only select into status * from... works.
Imre Horvath
2010. 08. 18, szerda keltezéssel 09.42-kor Sergey Konoplev ezt írta:
> Hi,
>
> SELECT column_name
> INTO var_name
> FROM ...
>
> 2010/8/17 Horváth Imre :
> > Hi!
> >
> > My question is, how can I get the out parameter from
Hi,
SELECT column_name
INTO var_name
FROM ...
2010/8/17 Horváth Imre :
> Hi!
>
> My question is, how can I get the out parameter from a function with
> SELECT INTO by name?
> I mean:
>
> create function testfunc1(OUT _status integer) returns integer as
> $BODY$
> _status := 0;
> $BODY$
> l
Hello
It cannot work, you mix the sql with plpgsql language
2010/8/17 Imre Horvath :
> Hi!
>
> My question is, how can I get the out parameter from a function with
> SELECT INTO by name?
> I mean:
>
> create function testfunc1(OUT _status integer) returns integer as
> $BODY$
> _status :=
On 2010-03-25, Leif Biberg Kristensen wrote:
> I'm struggling with how to make plpgsql iterate through a list of numbers
> input as a text string, eg. "1438 2656 973 4208". I figure that I can use the
> regexp_split_to_array() function to make an array of the string, but can I
> iterate through
On Thursday 25. March 2010 16.16.53 Leif Biberg Kristensen wrote:
> I'm struggling with how to make plpgsql iterate through a list of numbers
> input as a text string, eg. "1438 2656 973 4208". I figure that I can use the
> regexp_split_to_array() function to make an array of the string, but can
On 02/10/2010 08:29 AM, Andrea Visinoni wrote:
hi,
i have a table called "zones": idzone, zone_name
and several tables called zonename_records (same structure), where
zonename is one of the zone_name in the "zones" table.
What i want to do is a function that union all of this tables
dinamically b
On 2/10/2010 11:29 AM, Andrea Visinoni wrote:
> hi,
> i have a table called "zones": idzone, zone_name
> and several tables called zonename_records (same structure), where
> zonename is one of the zone_name in the "zones" table.
> What i want to do is a function that union all of this tables
> di
On Tuesday 1. September 2009, Ian Barwick wrote:
>This seems to do what you want:
>
> my_int := (REGEXP_MATCHES(txt, E'^#(\\d+)'))[1];
Great! I had no idea that REGEXP_MATCHES() could do that kind of stuff.
pgslekt=> select (REGEXP_MATCHES('#42 blabla', E'^#(\\d+)'))
[1]::integer;
regexp_match
2009/9/1, Leif B. Kristensen :
> In Plpgsql, I've got this problem of how to assign an integer extracted
> from a regex to a variable. My approach so far feels kludgy:
>
> -- extract ^#(\d+) from txt
> IF txt SIMILAR TO E'#\\d+%' THEN
> my_int := SUBSTR(SUBSTRING(txt, E'#\\d+'), 2,
> LE
Tom Lane wrote:
"Matthew T. O'Connor" writes:
I want the following:
select column_to_english_list( select towns from towns_table );
to return:
'town1, town2 and town3'
I wonder though if it wouldn't be better to recast the problem as an
aggregate:
select column_to_english_list(towns) from
"Matthew T. O'Connor" writes:
> I want the following:
> select column_to_english_list( select towns from towns_table );
> to return:
> 'town1, town2 and town3'
> In order to do this, I think I would have to create a pl/pgsql function
> that accpts a setof text argument, but I'm not sure that's
"Sabin Coanda" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> "Sabin Coanda" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> ...
>>
>> How can I get my desired function that means when I call test( 'a\b' ) it
>> will return 'a\\b' ?
>>
>
...
> CREATE OR REPLACE
"Sabin Coanda" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
...
>
> How can I get my desired function that means when I call test( 'a\b' ) it
> will return 'a\\b' ?
>
The problem seems to be the constant evaluation in plpgsql functions which
is not aware of standard_conforming_
yeah i noticed that this morning, it used to be a while loop, for some
reason (probably parser related) it keeps giving me an error on the exit
when statement in the loop.
On Wed, 2007-04-25 at 09:38 -0400, John DeSoi wrote:
> One problem (unless you intend to only look at every other element)
>
One problem (unless you intend to only look at every other element)
is that you are incrementing idxptr explicitly in your loop. The FOR
loop does that for you. This is the reason your output shows only
even values.
John
On Apr 24, 2007, at 4:42 PM, Richard Albright wrote:
for i
If you use a plpgsql function to select the row you want to validate,
it will make life much easier. Something like
...
$$
declare
my_row a_row_type;
is_ok integer;
begin
select into my_row * from a_row_type where
is_ok := my_a_validate(my_row);
return is_ok;
$$
...
On Apr 4, 20
am Tue, dem 03.04.2007, um 13:19:26 -0700 mailte Karthikeyan Sundaram
folgendes:
> Thank you very much. It works. I am not doing any insert or update hence I
> cannot create a trigger. But my another question is
>
> How will I pass the values to Foo parameters.
>
> I mean
>
> I want to p
It should work pretty much like you have it. You don't need a type;
the table is already a type.
Something like:
create or replace function a_func (in p_row a) returns int as
$$
if p_row.i ...
if p_row.j ...
$$
If it does not work, show the error and I'll try to dig up an example.
John
the
table-structure. Than you can create your function.
test=# create type a as ( i int, j varchar(20), k date);
CREATE TYPE
test=*# create function my_a (IN foo a) returns int as $$begin return 1; end;
$$ language plpgsql;
CREATE FUNCTION
test=*#
> > Date: Tue, 3 Apr 2007 20:18:43 +0200
Date: Tue, 3 Apr 2007 20:18:43 +0200> From: [EMAIL PROTECTED]> To:
> pgsql-sql@postgresql.org> Subject: Re: [SQL] plpgsql function question> >
> Karthikeyan Sundaram <[EMAIL PROTECTED]> schrieb:> > > > > Hi,> > > > I am
> having a requireme
Karthikeyan Sundaram <[EMAIL PROTECTED]> schrieb:
>
> Hi,
>
> I am having a requirement here.
>
> 1) I need to write a plpgsql function where it takes the input
> parameter of a structure of a table.
Because? To build this table? You can pass an ascii-text
On Sat, Mar 31, 2007 at 12:43:44AM -0700, Karthikeyan Sundaram wrote:
> Hi, I am using Postgres 8.1.0. I have a requirement. I will
> create a function accepting few parameters. This will check into
> various tables and give back an array of values. I want to use the
> pgpsql block. I know
On 10/18/06, Andy <[EMAIL PROTECTED]> wrote:
Hi, I have the following function:
CREATE OR REPLACE FUNCTION zahlavis_rech_list(int4,
varchar(10)) RETURNS "varchar" AS$BODY$DECLAREavis_id ALIAS FOR
$1;rech_type ALIAS FOR $2;rech_list text;sql text;rec
RECORD;BEGIN
rech_list := '';sql :=
Hi, Chester,
chester c young wrote:
> is is possible for to have a "do instead" trigger on a view that is a
> plpgsql function?
Kinda.
They're called "rules", not "triggers". See
http://www.postgresql.org/docs/8.1/interactive/rules.html
HTH,
Schabi
--
Markus Schaber | Logical Tracking&Tracing
On 9/16/05, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Fri, Sep 16, 2005 at 07:04:39PM -0400, Kenneth Dombrowski wrote:
> > create or replace function update_rate (integer, integer, integer,
> > integer, numeric, integer)
>
> You've created a function with six arguments.
>
> > invoicer=
On Fri, Sep 16, 2005 at 07:04:39PM -0400, Kenneth Dombrowski wrote:
> create or replace function update_rate (integer, integer, integer,
> integer, numeric, integer)
You've created a function with six arguments.
> invoicer=> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216);
> E
On Fri, 16 Sep 2005, Kenneth Dombrowski wrote:
> I can't get this one to work at all:
>
> create or replace function update_rate (integer, integer, integer,
> integer, numeric, integer)
> returns void
> as '
> declare
> x_admin_id alias for $1;
>
Ganesh,
Did you have a look at example Example 35-2. A PL/pgSQL
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
Regds
maLLAH
---(end of broadcast)---
TIP 8: explain analyze is your friend
On Thu, Jun 02, 2005 at 01:57:26PM +0200, Bart Degryse wrote:
>
> I'm looking for a way to use a parameter given to a trigger function as
> fieldname. It should be something like
> create function f_makeupper() returns trigger as '
> begin
> NEW.TG_ARGV[0] := upper(NEW.TG_ARGV[0]);
> RETURN
On Tue, 8 Mar 2005, Fatih Cerit wrote:
> I have a function and I want to update a table's two rows but having problem
> with plpgsql & date-time functions. First field of the table must be now()
> this is ok.. but the second field must be now() + '60 days' if the query
> like this : SELECT INTO to
x27;C' functions :/
Here is where I'd like to get your expert opinions (thank you very much in
advance for all your efforts), so I can get started fast.
Thanks,
Peter
From: Michael Fuhr <[EMAIL PROTECTED]>
To: Peter Manchev <[EMAIL PROTECTED]>
CC: pgsql-sql@postgresql.org
On Thu, Feb 03, 2005 at 05:31:47AM -0800, Peter Manchev wrote:
> I believe the functionality I need (hiding the function code from users)
Why do you want to hide the function code? Does it contain sensitive
data? As I asked before, what problem are you trying to solve?
--
Michael Fuhr
http:/
On Wed, Feb 02, 2005 at 05:57:10AM -0800, Peter Manchev wrote:
> I would like to convert all my plpgsql functions to their 'C' equivalent
> coded functions.
Why? What problem are you trying to solve?
> Does anyone have experience in this matter?
See "C-Language Functions" in the documentation
Don Drake <[EMAIL PROTECTED]> writes:
> On Mon, 31 Jan 2005 16:32:02 -0800 (PST), Stephan Szabo
> <[EMAIL PROTECTED]> wrote:
>> Actually, now that I think about it, I wonder if that's a good thing to
>> use because I don't think that'll use indexes to do the search. You may
>> want to do some test
I'm constraining on other columns as well and it's still picking up the index.
Thanks again.
-Don
On Mon, 31 Jan 2005 16:32:02 -0800 (PST), Stephan Szabo
<[EMAIL PROTECTED]> wrote:
> On Mon, 31 Jan 2005, Don Drake wrote:
>
> > You learn something new everyday. I've never seen that syntax befo
On Mon, 31 Jan 2005, Don Drake wrote:
> You learn something new everyday. I've never seen that syntax before,
> and it works like a charm!!
Actually, now that I think about it, I wonder if that's a good thing to
use because I don't think that'll use indexes to do the search. You may
want to do
You learn something new everyday. I've never seen that syntax before,
and it works like a charm!!
Thanks a ton.
-Don
On Mon, 31 Jan 2005 13:31:34 -0800 (PST), Stephan Szabo
<[EMAIL PROTECTED]> wrote:
>
> On Sun, 30 Jan 2005, Don Drake wrote:
>
> > OK, I have a function that finds records tha
On Sun, 30 Jan 2005, Don Drake wrote:
> OK, I have a function that finds records that changed in a set of
> tables and attempts to insert them into a data warehouse.
>
> There's a large outer loop of candidate rows and I inspect them to see
> if the values really changed before inserting.
>
> My
As far as I know, you didn't post your actual table definitions (or
full queries) earlier, so I'm not exactly sure what you mean.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-00
My outer query to get the candidates has an outer join, that works
just fine and I get the null OMCR_ID's.
It's when I have to query the dimension table (no joins) to see if a
row exists with a (sometimes) null OMCR_ID I'm forced to write 2
queries, when I think I should only have to write one.
T
This sounds like a perfect candidate for a LEFT OUTER JOIN. See:
http://www.postgresql.org/docs/7.4/static/queries-table-
expressions.html#QUERIES-FROM
Yours would looks something like:
SELECT *
FROM ...
LEFT JOIN candidate AS c
ON <...>.omcr_id = c.omcr_id
AND ...
-tfo
--
Thomas F. O'Connell
Co-
Try removing the comma after varz
SELECT into varx, vary, varz,
colx, coly, colz, FROM
I've tried parens and various other things but no luck.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TI
On Sat, 15 Jan 2005, Ari Kahn wrote:
> CREATE FUNCTION gets_nooky() returns numeric AS
> '
> DECLARE
> i integer;
> gt1cnt record;
> gt1 record;
> cluster record;
> cluster_cnt integer;
> slocus integer;
> minmax record;
>
> BEGIN
> SELECT INTO gt1 * FROM c
Oops! [EMAIL PROTECTED] ("Yudie") was seen spray-painting on a wall:
> How in plpgsql use LIKE with a variable?
>
> let say I want to do this query:
> SELECT INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT 1;
>
> keyword is a variable, in this case I want to find name like 'Jo%'
>
Try:
SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || ''%'' LIMIT
1;
You append KEYWORD and a '%' together using ||. You need to use
doubled quotes inside the quoted environment; one gets stripped off so
that the stored procedure will contain the query
SELECT INTO RS ID FRO
On Thu, Dec 16, 2004 at 05:53:43PM -0600, Yudie wrote:
> How in plpgsql use LIKE with a variable?
>
> let say I want to do this query:
>
> SELECT INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT 1;
>
> keyword is a variable, in this case I want to find name like 'Jo%'
Use the
Marinos Yannikos <[EMAIL PROTECTED]> writes:
> create function blup_unique2 (text,text) returns boolean as 'begin
> perform (select 1 from blup where t1=$1 or t1=$2 or t2=$1 or t2=$2 or
> $1=$2 limit 1); return NOT FOUND; end' LANGUAGE plpgsql;
You've got a syntax problem. PERFORM is syntactica
Marinos Yannikos wrote:
(btw.: I'm trying a few ways to ensure that all values in both t1 and t2
are unique:
alter table blup add constraint check (blup_unique3(t1,t2));
- perhaps there are more elegant ways, any suggestions?)
No doubt someone will tell me this is Very Wrong:
create table blup_t
On Sun, Sep 26, 2004 at 06:15:57PM +0200, Sz?lka Tam?s wrote:
> I have a table with field named "XY_1", "XY_2", ... "XY_255".
> I want to access the values in the table from a plpgsql function somehow
> in a loop with the iterative variable. Can I address the appropriate
> ''XY_'' || loopvaria
Kemin Zhou <[EMAIL PROTECTED]> writes:
> I just want to bring up a point for discussion:
> for the function definition is treated as a long string right now. Why
> not add an additional token so that we don't have to write 10 single quotes.
Something's already been done about this for 8.0.
for example
create function a_func() return integer as
.
end a_func
after as and before end a_func marks the definition body. Would this be
better than the ' '?
I guess having a parser that gave better error messages would be nice,
too.
Yasir
---(end of broadcast)
Phil> execute ''column_values :=
Phil> column_values || quote_literal(r.'' || cr.column_name || '')'';
basic> FOR rec IN EXECUTE
basic> ''select column_values || quote_literal(r.'' ||
basic> cr.column_name || '') alias column_values''; LOOP
basic> column_values := rec.column_values;
basi
"Phil Endecott" <[EMAIL PROTECTED]> writes:
> I think what I really need is an introspection mechanism
> so that I can loop over each element of the record and construct the
> insert as a string. Maybe this is possible using a different
> server-side language?
pltcl can probably handle this; I'm
Phil Endecott wrote:
> !! execute ''column_values :=
> !! column_values || quote_literal(r.'' || cr.column_name || '')'';
I'm guessing you want something like
FOR rec IN EXECUTE ''select column_values || quote_literal(r.'' || cr.column_name ||
'') alias column_values''; LOOP
column_values
I wrote:
> perhaps rather than inspecting the record variable to see what fields
> it contains, I can look at the table to see what columns it contains
This is my effort. It doesn't work, because I can only EXECUTE
SQL statements, not PLPGSQL statements. I need to EXECUTE an
assignment statement
Phil> Insert from a record variable?
Riccardo> Try insert into table select r.*;
Tom> in 7.5
Tom> insert into table select r.*;
Tom> insert into table values(r.*);
Tom> so long as r is declared to be of a named rowtype (not just
Tom> RECORD)
Thanks! Unfortunately I need record, rather
"Riccardo G. Facchini" <[EMAIL PROTECTED]> writes:
> --- Phil Endecott <__> wrote:
>> I'm writing a simple plpgsql function that selects a row from a
>> table, modifies it slightly, and inserts the modified version. This
>> sounds simple, but I can't find how to do the insert in a simple
>> generi
--- Phil Endecott <__> wrote:
> Dear Postgresql experts,
>
> I'm writing a simple plpgsql function that selects a row from a
> table, modifies it slightly, and inserts the modified version. This
> sounds simple, but I can't find how to do the insert in a simple
> generic fashion: although SELECT
On Wednesday 31 March 2004 12:07, Ivan Sergio Borgonovo wrote:
>
> I was used to do stuff like this with MS SQL
>
> create proc sp_getuid @uid uniqueidentifier
[snip]
> Does it mean that to have variables in SP I'll have to use plpgsql
> in spite of plain SQL?
Yep - just like there you were using
On Wed, 31 Mar 2004 03:31:01 -0500
Christopher Browne <[EMAIL PROTECTED]> wrote:
> The forthcoming support for recursive queries using a WITH clause
> might provide, after a fashion, a way to declare variables.
I think I'll have to work with pg 7.3
Does this translate to: you won't be able to use
[EMAIL PROTECTED] (Ivan Sergio Borgonovo) wrote:
> Is there a way to declare variables and use IF in plain SQL, not in
> plpgsql inside stored procedures?
The forthcoming support for recursive queries using a WITH clause
might provide, after a fashion, a way to declare variables.
As for IF, there
[EMAIL PROTECTED] wrote:
When I run the function below I recieve an error message saying that
column definitions need to be specified for a function returing a
type RECORD.
I was under the impression that the FOR row IN SELECT... loop would
assign a column structure to the RECORD type. Am I wrong a
Rich,
> That was my thought - I was fairly sure I fixed it though. Do, really
> need to be specific in terms of type in terms of int, or can I put numeric,
INT and numeric are fairly different, and I believe that SRF return types are
very fussy about data types; I wouldn't be surprised if y
Rich,
> I don't want to abuse you - so if this is the wrong forum for this, feel
> free to toss it back with direction :-)
No, but I do think that you should CC a mailing list becuase:
1) Someone else may have direct experience with your problem and answer more
quickly than me, and:
2) Other peo
On Tue, 21 Oct 2003, Josh Berkus wrote:
> >heute := ''today'';
> > Select Into vk ourcolumn From table where other = foo;
> >If vk > 0 Then
> > vk_txt := ''Vorkuehlung notwendig'';
> > ez := heute + interval ''vk days'';
>
> PL/pgSQL handles variable like SQL, not like PHP or Perl
On Tuesday 21 October 2003 14:58, Wilhelm Graiss wrote:
>heute := ''today'';
> Select Into vk ourcolumn From table where other = foo;
>If vk > 0 Then
> vk_txt := ''Vorkuehlung notwendig'';
> ez := heute + interval ''vk days'';
> The variable 'heute' is declared as timestamp,
Willhelm,
> Begin
>
>heute := ''today'';
> Select Into vk ourcolumn From table where other = foo;
>If vk > 0 Then
> vk_txt := ''Vorkuehlung notwendig'';
> ez := heute + interval ''vk days'';
PL/pgSQL handles variable like SQL, not like PHP or Perl. You can't do
>
> Thanks a lot for Your reply by I wanted to create some *foo* variable
(
> in declare part of pl/pgsql trigger function) and to set :
> foo := NEW
>
> That's why I've posted the link from interactive docs - there is the
> same question
>
> http://www.postgresql.org/docs/7.3/interactive/plpgsql-t
I would suggest to throw a error, or at least a warning.
This will FORCE people to program in the correct way.
I also thought that 'IF $1 THEN ...' should work ok but giving it a other
thought it's indeed stuped to write that way (I'm from the C world...)
Ries
-Oorspronkelijk bericht-
V
Define the language! If it breaks code, so be it.
2. Throw an error if the _expression_ doesn't return boolean.
Yes, yes, absolutely.
By definition "an IF, WHILE, or EXIT statement is a boolean _expression_"
SO
if "some stupid piece of text" THEN
should not compile, there is no BOOLEAN _expre
Manfred Koizar <[EMAIL PROTECTED]> writes:
> On Mon, 08 Sep 2003 11:40:32 -0400, Tom Lane <[EMAIL PROTECTED]>
> wrote:
>> 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans
>> will be accepted in exactly the same cases where they'd be accepted
>> in a boolean-requiring SQL constru
On Mon, 08 Sep 2003 11:40:32 -0400, Tom Lane <[EMAIL PROTECTED]>
wrote:
>4. Use the parser's coerce_to_boolean procedure, so that nonbooleans
> will be accepted in exactly the same cases where they'd be accepted
> in a boolean-requiring SQL construct (such as CASE). (By default,
> none are,
"Marek Lewczuk" <[EMAIL PROTECTED]> writes:
> CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
> BEGIN
> IF $1 THEN
> RETURN $1;
> ELSE
> RETURN $2;
> END IF;
> END;
> 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
If there's a bug here at all, it's
On Wednesday 02 July 2003 18:49, Andreas Schmitz wrote:
> Hello *,
>
> I have a litlle problem writing a plpgsql trigger function. I am in need to
> get some interpretaion of the TG_OP within an IF clause to decide with
> action will be taken. I tried it that way:
>
> IF (TG_OP=DELETE AND
"Knut P. Lehre" <[EMAIL PROTECTED]> writes:
> Using pgsql 7.2.1 (PeerDirect beta4) on WinXP Prof SP1. Client: MS
> In plpgsql, I can access columns with a name containing capital letters by
> enclosing the name in double quotes: tbl."ColName"
> However, if the quoted name contains spaces or hyph
Can you post the recursion function that you use on this case to get out
the information from the table ?
Thanks,
Luis Sousa
Stefano Vita Finzi wrote:
Greetings!
I have a table like:
node parent
1 2
2 3
3 4
Since i traverse this table with a recursive function, i want to
Frankie wrote:
The case is when I call a function (from Server 1 at psql prompt) that will
call dblink to do some operation
on another server(it will take certain time), i.e. Server 2, and meanwhile I
just unplug the network cable to Server 2.
The consequence is that the function will never return
1 - 100 of 137 matches
Mail list logo
