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,
(sorry my previous email was truncated)
hi,
Here is what I want to do :
I want to check each row of a table against some conditions (this
check needs some
processing stuff I can easily code with pl/pgsql).
If the row is OK, I want to add it in a "resulting table",
else I just ignore the current
hi,
say I have the following (simplified for discussion) pl/pgsql function:
FUNCTION myfunction ( ...) RETURNS TABLE ( elem1 integer, elem2 text, ...)
DECLARE
g RECORD
BEGIN
FOR g in SELECT colum1, column2 FROM someTable
LOOP
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
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:
> ---
Dear all,
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
We have a plpgsql function called paymentcalc, which calculates the
payment necessary to pay off a loan. It's defined like this:
CREATE OR REPLACE FUNCTION paymentcalc(IN amount numeric, IN
interestrate numeric, IN termmonths integer, IN paymentfreq integer, IN
dueday1 integer, IN dueday2 i
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 ?
>
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 ?
Thanks in advance,
Chuck
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
Hi,
I'm trying to run an execute with a dynamic sql command within a function.
I need a clean exception handling here, but my version does not work
somehow.
I want to trap a possible syntax error and write the error code, error
message and the sql into a table.
the function looks similar to this
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
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$
language plpgsql;
create function testfunc2() as
declare
status integer;
$BODY$
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 :=
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$
language plpgsql;
create function testfunc2() as
declare
status integer;
$BODY$
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
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 an array with eg. a FOR loop?
regards,
--
Leif Bibe
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
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 based on "zones" table, this is what i've done
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
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,
LENGTH(SUBSTRING(txt, E'#\\d+')) -1)::INTEGER
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
Hello, I'm trying to write a pl/pgsql function that takes in a column
(setof?) of text values and returns an english language list, for example:
if "select towns from towns_table;" retuned
town1
town2
town3
I want the following:
select column_to_english_list( select towns from towns_table );
t
"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_
Hi there,
Having standard_conforming_strings = 'on', I build the following scenario.
I request SELECT replace( 'a\b', '\', '\\' ), which get me the result:
replace
-
a\\b
I'd like to build a function that give me the same result, as:
CREATE OR REPLACE FUNCTION "test"(s varchar)
RET
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
I am attempting to create a moving average elements function that will
return a set of elements in a particular moving average and am having
difficulty iterating thrrough the passed in array properly. Any help
would be greatly appreciated.
code below...
select getmovavgelements( aggarray(trade_d
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
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.
2) The table has 15 columns
3) It does lots of validation based on the parameter and finally returns an
integer as output parameters
Q) Ho
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
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 that we
can create using language sql. Is it possible to return an ar
Hi All,I have a stored procedure that fails and don't know how to fix it, so I hope to find some help here.I works on a table called 'locations' that looks like this:design=# \d locations Table "public.locations" Column | Type | Modifiers-+---
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, 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 := 'SELECT '|| rech_type
||' as xx FROM rechnung W
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
is is possible for to have a "do instead" trigger on a view that is a
plpgsql function?
__
Yahoo! DSL Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
---(end of broadcast)---
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;
>
Hi,
I'm using the postgresql 7.4.7-6sarge1 package from debian stable.
I am confused about these two functions, and their behavior when being
given NULL values in place of a parameter declared as integer:
This one works as expected:
create or replace function insert_rate (integer, integer, in
Can I do something like this:
CREATE TABLE sample (id SERIAL, node INTEGER, parent INTEGER);
INSERT INTO sample(node,parent) VALUES(1,0);
INSERT INTO sample(node,parent) VALUES(2,0);
INSERT INTO sample(node,parent) VALUES(3,1);
INSERT INTO sample(node,parent) VALUES(4,3)
CREATE OR REPLACE FUNCTIO
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
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 NEW;
end;
' language 'plpgsql';
create trigger "TRIG_tbltest" before inse
Hello,
I have searched everywere
for a solution pls help me. I am building a trigger which would keep
track of every modification to some table. here i am generlising the
function. the following is the code in plpgsql.
--/* function for giving the field attributes of a given table */
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
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_day now() + interval '60 days' ; it works bu
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
I would like to convert all my plpgsql functions to their 'C' equivalent
coded functions.
Does anyone have experience in this matter?
Thank you,
Peter
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archive
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-
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 problem is that when I look to see if the row exists
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
The docs say:
The result of a SELECT command yielding multiple columns (but only one
row) can be assigned to a record variable, row-type variable, or list
of scalar variables. This is done by:
SELECT INTO target select_expressions FROM ...;
where target can be a record variable, a row variab
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
I have a single column result that I want to loop through using an
index. I also want to jump ahead in the result using the index (below:
i:=i+cluster_cnt-1;) . However, I'm getting the following error.
ERROR: syntax error at or near "SELECT" at character 9
CONTEXT: PL/pgSQL function "gets_noo
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
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%'
Full functio
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
1 - 100 of 208 matches
Mail list logo
