from:"Richard Albright"

Re: [SQL] Existential quantifier

2009-10-09 Thread Richard Albright

you could use distinct on

select distinct on (fs.film.title, fs.film.year ) title, year
from fs.film left join fs.star on fs.film.id = fs.star.film
  where fs.star.last = 'Sheen';

On Sat, 2009-10-10 at 00:53 +0200, Dag-Erling Smørgrav wrote:
> Consider the attached schema (filmstars.sql), which is a poorly designed
> database of films and actors.  The following query gives me a list of
> films in which either Charlie or Martin Sheen starred:
> 
> select fs.film.title, fs.film.year
>   from fs.film left join fs.star on fs.film.id = fs.star.film
>   where fs.star.last = 'Sheen'
>   group by fs.film.title, fs.film.year;
> 
> Is there a way to do this without the "group by" clause?
> 
> DES
> plain text document attachment (filmstars.sql)
> drop schema fs cascade;
> 
> create schema fs;
> 
> create table fs.film (
>   id serial not null primary key,
>   title varchar not null unique,
>   year integer not null
> );
> 
> create table fs.star (
>   id serial not null primary key,
>   film integer not null references fs.film(id),
>   last varchar not null,
>   first varchar not null,
>   unique (film, last, first)
> );
> 
> insert into fs.film(title, year) values ('Apocalypse Now', 1979);
> insert into fs.star(film, last, first)
>   select id, 'Sheen', 'Martin' from fs.film where title = 'Apocalypse Now';
> insert into fs.star(film, last, first)
>   select id, 'Brando', 'Marlon' from fs.film where title = 'Apocalypse Now';
> insert into fs.star(film, last, first)
>   select id, 'Duvall', 'Robert' from fs.film where title = 'Apocalypse Now';
> insert into fs.star(film, last, first)
>   select id, 'Ford', 'Harrison' from fs.film where title = 'Apocalypse Now';
> 
> insert into fs.film(title, year) values ('Blade Runner', 1982);
> insert into fs.star(film, last, first)
>   select id, 'Ford', 'Harrison' from fs.film where title = 'Blade Runner';
> insert into fs.star(film, last, first)
>   select id, 'Young', 'Sean' from fs.film where title = 'Blade Runner';
> insert into fs.star(film, last, first)
>   select id, 'Hauer', 'Rutger' from fs.film where title = 'Blade Runner';
> insert into fs.star(film, last, first)
>   select id, 'Hannah', 'Daryl' from fs.film where title = 'Blade Runner';
> 
> insert into fs.film(title, year) values ('Platoon', 1986);
> insert into fs.star(film, last, first)
>   select id, 'Sheen', 'Charlie' from fs.film where title = 'Platoon';
> insert into fs.star(film, last, first)
>   select id, 'Dafoe', 'Willem' from fs.film where title = 'Platoon';
> insert into fs.star(film, last, first)
>   select id, 'Berenger', 'Tom' from fs.film where title = 'Platoon';
> 
> insert into fs.film(title, year) values ('Wall Street', 1987);
> insert into fs.star(film, last, first)
>   select id, 'Douglas', 'Michael' from fs.film where title = 'Wall Street';
> insert into fs.star(film, last, first)
>   select id, 'Sheen', 'Charlie' from fs.film where title = 'Wall Street';
> insert into fs.star(film, last, first)
>   select id, 'Hannah', 'Daryl' from fs.film where title = 'Wall Street';
> insert into fs.star(film, last, first)
>   select id, 'Sheen', 'Martin' from fs.film where title = 'Wall Street';
-- 
Rick Albright
Senior Quantitative Analyst
Insiderscore LLC
[email protected]


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] pl/python out params

2010-08-07 Thread Richard Albright


first define a custom type, then drop the out parameters.

create type mytype as (
i integer,
j text );

create or replace function outtest()
 returns mytype as
$BODY$
i = 1
j = 'something'
return ( i, j )
$BODY$
language plpythonu;


select * from outtest();
 i | j
---+---
 1 | something
(1 row)


On 08/07/2010 10:49 AM, Imre Horvath wrote:

Hi!

Is there a way to use output parameters with a pl/python fucntion?
I've tried with no luck: if I define out parameters, it says return type
must be record, if I define a record return type, I get an error on
executing that pl/python doesn't support record return type...

My simple test func is:

create or replace function outtest(out i integer, out j text)
  returns boolean as
$BODY$
i = 1
j = 'something'
return True
$BODY$
language plpythonu;

Thanks in advance:
Imre Horvath


   



--
*Rick Albright*
Senior Quantitave Analyst
Web: www.insiderscore.com 
Email: [email protected]

Re: [SQL]How to transform table rows into Colum?

2011-03-17 Thread Richard Albright


you can also generate a crosstab table using the sign function

you can check out the link below ( its a sqlite tutorial, but the same 
idea will work for pg too )


http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html


On 03/09/2011 12:16 PM, Eric Ndengang wrote:

Hi Guys,
I have the following table:

Name   Value   rang  salary

name1   value1  12500
name2   value2  22600
name3   value 3 3300

and want to obtain the following result:

name1  name2  name3

value1  value2   value3
1 23
2500  2600300

what should i do ? I read about the crosstab function  in postgresql 
but still have no idea on how to use it to solve this problem.

Any help would be appreciated
Regards




--
*Rick Albright*
Senior Quantitative Analyst
Web: www.insiderscore.com 
Email: [email protected]

[SQL] plpgsql array looping

2007-04-24 Thread Richard Albright

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_date), aggarray(close_price),
'2004-02-10'::timestamp, 10 ) 
from 
( select trade_date, close_price::numeric 
from quotedata 
where symbol='MSFT'
and trade_date > '2004-01-01'
order by trade_date asc) values;

NOTICE:  idx: {"2004-01-02 00:00:00","2004-01-05 00:00:00",...}
NOTICE:  vals: {27.45,28.14,...}
NOTICE:  maxdate: 2004-02-10 00:00:00
NOTICE:  dims: [1:821] 
NOTICE:  idx ptr: 2
NOTICE:  idx ptr: 4
NOTICE:  idx ptr: 6
NOTICE:  idx ptr: 8
NOTICE:  idx ptr: 10
NOTICE:  idx ptr: 12
NOTICE:  idx ptr: 14
NOTICE:  idx ptr: 16
NOTICE:  idx ptr: 18
NOTICE:  idx ptr: 20
NOTICE:  idx ptr: 22
NOTICE:  idx ptr: 24
NOTICE:  idx ptr: 26
NOTICE:  row: 2004-01-02 00:00:00 27.45

ERROR:  array value must start with "{" or dimension information
CONTEXT:  PL/pgSQL function "getmovavgelements" line 25 at assignment

---

CREATE OR REPLACE FUNCTION getmovavgelements(p_idxarray _timestamp,
p_valarray _numeric, p_idx "timestamp", p_periods int8)
  RETURNS setof resultset AS
$BODY$
declare
idxptr int8;
idxendptr int8;
v_rec record;
v_rtn resultset%rowtype;
v_sql text;
begin
raise notice 'idx: %', p_idxarray;
raise notice 'vals: %', p_valarray;
idxptr := array_lower(p_idxarray, 1);
raise notice 'maxdate: %',  p_idx;
raise notice 'dims: % ', array_dims(p_idxarray);

for idxptr in 1 .. array_upper(p_idxarray, 1) 
loop
exit when p_idxarray[idxptr] >= p_idx;
idxptr := idxptr +1;
raise notice 'idx ptr: %', idxptr;
end loop;
idxendptr := idxptr + p_periods;
for v_rec in 
select s.ser, p_idxarray[s.ser] as index, p_valarray[s.ser] as 
value
from generate_series(idxptr, idxendptr) as s(ser)
loop
raise notice 'row: % %', v_rec.index, v_rec.value;
v_rtn := (v_rec.index, v_rec.value);
return next v_rtn;
end loop;
end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-- 
Rick Albright
Senior Quantitative Analyst
Indie Research, LLC
254 Witherspoon Street
Princeton, NJ 08542
(609)497-1030
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] plpgsql array looping

2007-04-25 Thread Richard Albright

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)  
> 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 idxptr in 1 .. array_upper(p_idxarray, 1)
> > loop
> > exit when p_idxarray[idxptr] >= p_idx;
> > idxptr := idxptr +1;
> > raise notice 'idx ptr: %', idxptr;
> > end loop;
> 
> 
> 
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
> 
> 
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
> 
> http://www.postgresql.org/about/donate
-- 
Rick Albright
Senior Quantitative Analyst
Indie Research, LLC
254 Witherspoon Street
Princeton, NJ 08542
(609)497-1030
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

[SQL] sql wrapped plpgsql set returning function

2007-04-25 Thread Richard Albright

I have figured out my looping issue, but am having difficulty wrapping
my set returning plpgsql function getmovavgset with a getmovavg sql func
when i run the following:

select getmovavg(aggarray(trade_date), aggarray(close_price),
'2004-01-20', 5)
from 
( select trade_date, close_price::numeric 
from quotedata 
where symbol='MSFT'
and trade_date > '2004-01-01'
order by trade_date desc) values 

i get the following output:

NOTICE:  v_rec: ("2004-01-20 00:00:00",27.6917)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-16 00:00:00",27.6183)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-15 00:00:00",27.6767)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-14 00:00:00",27.7883)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-13 00:00:00",27.8783)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-12 00:00:00",27.9967)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-09 00:00:00",27.9767)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-08 00:00:00",28.0400)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-07 00:00:00",28.0100)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-06 00:00:00",27.9433)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-05 00:00:00",27.7950)
CONTEXT:  SQL function "getmovavg" statement 1

ERROR:  set-valued function called in context that cannot accept a set

I am having difficulty determining if the error is in my getmovavgset or
getmovavg function.
the notice msgs are coming from the getmovavgset func, so it is
iterating. I just dont know if the syntax is correct for the
generate_series statement in that func.  What am I missing? code is
below.

CREATE TYPE resultset AS
   ("index" timestamp[],
"values" numeric[]);

CREATE TYPE resultsetitem AS
   ("index" timestamp,
value numeric);

CREATE AGGREGATE aggarray(
  BASETYPE=anyelement,
  SFUNC=array_append,
  STYPE=anyarray,
  INITCOND='{}'
);

CREATE OR REPLACE FUNCTION getmovavgarray(p_idxarray _timestamp,
p_valarray _numeric, p_idx "timestamp", p_periods int8)
  RETURNS resultset AS
$BODY$
declare
idxptr int8;
idxendptr int8;
offsetptr int8;
begoffset int8;
ar_idx timestamp[]:='{}';
ar_values numeric[]:='{}';
v_rec resultset%rowtype;
v_rtn resultset%rowtype;
v_sql text;
v_index timestamp;
v_value numeric;
v_idx timestamp;
begin   
for offsetptr in 1 .. array_upper(p_idxarray, 1)
loop
--raise notice 'offset: %', offsetptr;
begoffset := offsetptr;
exit when p_idxarray[offsetptr]::timestamp <= p_idx;
end loop;
--raise notice 'offset: %, end: %', begoffset, array_upper(p_idxarray,
1);
for idxptr in 1 .. (array_upper(p_idxarray, 1) - begoffset)
loop
idxendptr := idxptr + p_periods;
v_index := p_idxarray[(idxptr + begoffset - 1)];
v_value := movavg(p_valarray[ (idxptr + begoffset - 1) : 
(idxendptr +
begoffset -1) ]);
ar_idx := array_append(ar_idx, v_index);
ar_values := array_append(ar_values, v_value);
--raise notice 'idx: %, avg: %', v_index, v_value;
end loop;
v_rtn := (ar_idx, ar_values);
return v_rtn;


end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION getmovavgset(p_idxarray _timestamp,
p_valarray _numeric, p_idx "timestamp", p_periods int8)
  RETURNS SETOF resultsetitem AS
$BODY$
declare
results resultset;
v_rec record;
v_rtn resultsetitem%rowtype;
v_sql text;
ar_idx timestamp[];
ar_values numeric[];
begin
--raise notice 'idxarray: %', p_idxarray;
for results in 
select * from getmovavgarray(p_idxarray, p_valarray, p_idx, 
p_periods)
loop
ar_idx := results.index;
ar_values := results.values;
end loop;
for v_rec in 
select (ar_idx)[s] as index, (ar_values)[s] as value from
generate_series(1, array_upper(ar_idx, 1)) as s
loop
raise notice 'v_rec: %', v_rec;
v_rtn := (v_rec.index, v_rec.value);
--raise notice 'resultset: %', v_rtn;
return next v_rtn;
end loop;
end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION getmovavg(p_idxarray _timestamp, p_valarray
_numeric, p_idx "timestamp", p_periods int8)
  RETURNS SETOF resultsetitem as
$BODY$
select * from getmovavgset($1, $2, $3, $4);
$BODY$
 LANGUAGE 'sql' volatile;
-

Re: [SQL] sql wrapped plpgsql set returning function

2007-04-25 Thread Richard Albright

It turns out that the from subselect is causing the error in :

select getmovavg(aggarray(trade_date), aggarray(close_price),
> '2004-01-20', 5)
> from 
>   ( select trade_date, close_price::numeric 
>   from quotedata 
>   where symbol='MSFT'
>   and trade_date > '2004-01-01'
>   order by trade_date desc) values 

whereas 

select * from getmovavg(array['2007-04-03', '2007-04-02',
'2007-04-01']::timestamp[], array[4.0,3.0,2.0]::numeric[], '2007-04-03',
3)

will work. anyone know why that would be?

On Wed, 2007-04-25 at 14:10 -0400, Richard Albright wrote:
> I have figured out my looping issue, but am having difficulty wrapping
> my set returning plpgsql function getmovavgset with a getmovavg sql func
> when i run the following:
> 
> select getmovavg(aggarray(trade_date), aggarray(close_price),
> '2004-01-20', 5)
> from 
>   ( select trade_date, close_price::numeric 
>   from quotedata 
>   where symbol='MSFT'
>   and trade_date > '2004-01-01'
>   order by trade_date desc) values 
> 
> i get the following output:
> 
> NOTICE:  v_rec: ("2004-01-20 00:00:00",27.6917)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-16 00:00:00",27.6183)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-15 00:00:00",27.6767)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-14 00:00:00",27.7883)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-13 00:00:00",27.8783)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-12 00:00:00",27.9967)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-09 00:00:00",27.9767)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-08 00:00:00",28.0400)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-07 00:00:00",28.0100)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-06 00:00:00",27.9433)
> CONTEXT:  SQL function "getmovavg" statement 1
> NOTICE:  v_rec: ("2004-01-05 00:00:00",27.7950)
> CONTEXT:  SQL function "getmovavg" statement 1
> 
> ERROR:  set-valued function called in context that cannot accept a set
> 
> I am having difficulty determining if the error is in my getmovavgset or
> getmovavg function.
> the notice msgs are coming from the getmovavgset func, so it is
> iterating. I just dont know if the syntax is correct for the
> generate_series statement in that func.  What am I missing? code is
> below.
> 
> CREATE TYPE resultset AS
>("index" timestamp[],
> "values" numeric[]);
> 
> CREATE TYPE resultsetitem AS
>("index" timestamp,
> value numeric);
> 
> CREATE AGGREGATE aggarray(
>   BASETYPE=anyelement,
>   SFUNC=array_append,
>   STYPE=anyarray,
>   INITCOND='{}'
> );
> 
> CREATE OR REPLACE FUNCTION getmovavgarray(p_idxarray _timestamp,
> p_valarray _numeric, p_idx "timestamp", p_periods int8)
>   RETURNS resultset AS
> $BODY$
> declare
>   idxptr int8;
>   idxendptr int8;
>   offsetptr int8;
>   begoffset int8;
>   ar_idx timestamp[]:='{}';
>   ar_values numeric[]:='{}';
>   v_rec resultset%rowtype;
>   v_rtn resultset%rowtype;
>   v_sql text;
>   v_index timestamp;
>   v_value numeric;
>   v_idx timestamp;
> begin 
>   for offsetptr in 1 .. array_upper(p_idxarray, 1)
>   loop
>   --raise notice 'offset: %', offsetptr;
>   begoffset := offsetptr;
>   exit when p_idxarray[offsetptr]::timestamp <= p_idx;
>   end loop;
>   --raise notice 'offset: %, end: %', begoffset, array_upper(p_idxarray,
> 1);
>   for idxptr in 1 .. (array_upper(p_idxarray, 1) - begoffset)
>   loop
>   idxendptr := idxptr + p_periods;
>   v_index := p_idxarray[(idxptr + begoffset - 1)];
>   v_value := movavg(p_valarray[ (idxptr + begoffset - 1) : 
> (idxendptr +
> begoffset -1) ]);
>   ar_idx := array_append(ar_idx, v_index);
>   ar_values := array_append(ar_values, v_value);
>

Re: [SQL] sql wrapped plpgsql set returning function

2007-04-25 Thread Richard Albright

I narrowed it down further.  Can someone explain the difference between
passing array[...] and passing an array using an aggregate array
function into the function?

On Wed, 2007-04-25 at 14:45 -0400, Richard Albright wrote:
> It turns out that the from subselect is causing the error in :
> 
> select getmovavg(aggarray(trade_date), aggarray(close_price),
> > '2004-01-20', 5)
> > from 
> > ( select trade_date, close_price::numeric 
> > from quotedata 
> > where symbol='MSFT'
> > and trade_date > '2004-01-01'
> > order by trade_date desc) values 
> 
> whereas 
> 
> select * from getmovavg(array['2007-04-03', '2007-04-02',
> '2007-04-01']::timestamp[], array[4.0,3.0,2.0]::numeric[], '2007-04-03',
> 3)
> 
> will work. anyone know why that would be?
> 
> On Wed, 2007-04-25 at 14:10 -0400, Richard Albright wrote:
> > I have figured out my looping issue, but am having difficulty wrapping
> > my set returning plpgsql function getmovavgset with a getmovavg sql func
> > when i run the following:
> > 
> > select getmovavg(aggarray(trade_date), aggarray(close_price),
> > '2004-01-20', 5)
> > from 
> > ( select trade_date, close_price::numeric 
> > from quotedata 
> > where symbol='MSFT'
> > and trade_date > '2004-01-01'
> > order by trade_date desc) values 
> > 
> > i get the following output:
> > 
> > NOTICE:  v_rec: ("2004-01-20 00:00:00",27.6917)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-16 00:00:00",27.6183)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-15 00:00:00",27.6767)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-14 00:00:00",27.7883)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-13 00:00:00",27.8783)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-12 00:00:00",27.9967)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-09 00:00:00",27.9767)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-08 00:00:00",28.0400)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-07 00:00:00",28.0100)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-06 00:00:00",27.9433)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > NOTICE:  v_rec: ("2004-01-05 00:00:00",27.7950)
> > CONTEXT:  SQL function "getmovavg" statement 1
> > 
> > ERROR:  set-valued function called in context that cannot accept a set
> > 
> > I am having difficulty determining if the error is in my getmovavgset or
> > getmovavg function.
> > the notice msgs are coming from the getmovavgset func, so it is
> > iterating. I just dont know if the syntax is correct for the
> > generate_series statement in that func.  What am I missing? code is
> > below.
> > 
> > CREATE TYPE resultset AS
> >("index" timestamp[],
> > "values" numeric[]);
> > 
> > CREATE TYPE resultsetitem AS
> >("index" timestamp,
> > value numeric);
> > 
> > CREATE AGGREGATE aggarray(
> >   BASETYPE=anyelement,
> >   SFUNC=array_append,
> >   STYPE=anyarray,
> >   INITCOND='{}'
> > );
> > 
> > CREATE OR REPLACE FUNCTION getmovavgarray(p_idxarray _timestamp,
> > p_valarray _numeric, p_idx "timestamp", p_periods int8)
> >   RETURNS resultset AS
> > $BODY$
> > declare
> > idxptr int8;
> > idxendptr int8;
> > offsetptr int8;
> > begoffset int8;
> > ar_idx timestamp[]:='{}';
> > ar_values numeric[]:='{}';
> > v_rec resultset%rowtype;
> > v_rtn resultset%rowtype;
> > v_sql text;
> > v_index timestamp;
> > v_value numeric;
> > v_idx timestamp;
> > begin   
> > for offsetptr in 1 .. array_upper(p_idxarray, 1)
> > loop
> > --raise notice 'offset: %', offsetptr;
> >

Re: [SQL] Possible to access value in RECORD without knowing column name?

2007-05-02 Thread Richard Albright

my_column = 'foo'
sql = 'select col1, col2, ' || my_column || ' as bar from mytable;
for myrecord in execute sql
loop
myvariable = myrecord.bar
end loop;

On Wed, 2007-05-02 at 12:17 -0700, Collin Peters wrote:
> In plpgsl, if I have a RECORD variable that is populated via some
> dynamic SQL, is it possible to access the columns in that RECORD
> object without knowing the column names?  I.e. Can I grab whatever
> value is the 3rd column?
> 
> random_colname = 'foobar';
> sql = 'SELECT col1, col2, ' || random_colname || ' FROM table';
> 
> FOR mviews IN EXECUTE sql LOOP
>--possible to access 3rd column of mviews?  something like mviews[3]?
> END LOOP;
> 
> Regards,
> Collin
> 
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
-- 
Rick Albright
Senior Quantitative Analyst
Indie Research, LLC
254 Witherspoon Street
Princeton, NJ 08542
(609)497-1030
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Existential quantifier

Re: [SQL] pl/python out params

Re: [SQL]How to transform table rows into Colum?

[SQL] plpgsql array looping

Re: [SQL] plpgsql array looping

[SQL] sql wrapped plpgsql set returning function

Re: [SQL] sql wrapped plpgsql set returning function

Re: [SQL] sql wrapped plpgsql set returning function

Re: [SQL] Possible to access value in RECORD without knowing column name?

9 matches

Site Navigation

Mail list logo

Footer information