Re: [SQL] Retrieve the column values of a record without knowing the names

2011-02-19 Thread Dmitriy Igrishin 
Hey,

2011/2/16 arthur_info 

>
> Hello,
>
> I've got the following function and I want to access the fields values of
> my
> record by index. The problem is that my select is retrieving each record
> line with all values and not each one of each row on my view... How can I
> solve this problem?
>
You can easily iterate across records from PL/pgSQL by using hstore, e.g:
SELECT (avals(hstore(ROW(83,6,4[3];
dmitigr=> SELECT (avals(hstore(ROW(83,6,4[3] AS thirdfield;
 thirdfield

 4
(1 row)

See http://www.postgresql.org/docs/9.0/static/hstore.html


>
> Thanks in advance.
>
>
> CREATE FUNCTION fc_teste_tce(aluno integer) RETURNS character varying AS
> $BODY$
> DECLARE
>  reg record;
> BEGIN
>  for reg in execute 'SELECT ARRAY (SELECT vw_teste_tce FROM
> estagio.vw_teste_tce where aluno = ''3043'' LIMIT 20) AS campos' loop
>for j in 1..array_upper(reg.campos,1) loop
>  raise notice 'Field Value: %',reg.campos[j];
>end loop;
>  end loop;
>  return 'ok';
> END;
> $BODY$
>
> LANGUAGE plpgsql VOLATILE;
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Retrieve-the-column-values-of-a-record-without-knowing-the-names-tp3387935p3387935.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



-- 
// Dmitriy.

Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause?

2011-02-19 Thread Dmitriy Igrishin 
Hey Gnanakumar,

You can wrap you UPDATE query into SQL function returning TABLE, e.g:

CREATE OR REPLACE FUNCTION public.update_mytable()
 RETURNS TABLE(email text, column1 text, column2 text, column3 text)
 LANGUAGE sql
AS $function$
UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL =
MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3;
$function$;

Next you can write, e.g:
SELECT DISTINCT * FROM
  (SELECT update_mytable()) AS foo(email, column1, column2, column3);

And so on.

2011/2/18 Gnanakumar 

> Any ideas?
>
> -Original Message-
> From: Gnanakumar [mailto:[email protected]]
> Sent: Thursday, February 17, 2011 12:36 PM
> To: [email protected]
> Subject: Is it possible to get DISTINCT rows from RETURNING clause?
>
> Hi,
>
> Is it possible to get DISTINCT rows from an UPDATE statement using
> RETURNING
> clause?
>
> "MYTABLE" columns are:
>APRIMARYKEYCOLUMN
>ABOOLEANCOLUMN
>EMAIL
>COLUMN1
>COLUMN2
>COLUMN3
>
> UPDATE using RETURNING clause query:
> UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL =
> MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3;
>
> Here in this case, I expect to return distinct rows from these columns:
> EMAIL, COLUMN1, COLUMN2, COLUMN3.
>
> I even tried out some ways of getting distinct rows, but it doesn't work.
> Though I can still solve this at application layer, I'm trying to find
> whether this could be controlled at query-level.  Any different
> ideas/suggestions are appreciated.
>
> Regards,
> Gnanam
>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



-- 
// Dmitriy.

Re: [SQL] Determine length of numeric field

2011-02-19 Thread Jasen Betts 
On 2011-02-15, Tony Capobianco  wrote:
> I'm altering datatypes in several tables from numeric to integer.  In
> doing so, I get the following error:
>
> dw=# \d uniq_hits
> Table "support.uniq_hits"
>Column   |  Type   | Modifiers 
> +-+---
>  sourceid   | numeric | 
>  hitdate| date| 
>  total  | numeric | 
>  hitdate_id | integer | 
> Indexes:
> "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace
> "support_idx"
> Tablespace: "support"
>
> esave_dw=# alter table uniq_hits alter sourceid type int;
> ERROR:  integer out of range
>
> Sourceid should not be more than 5 digits long.  I'm able to perform
> this query on Oracle and would like something similar on postgres 8.4:
>
> delete from uniq_hits where sourceid in (select sourceid from uniq_hits
> where length(sourceid) > 5);

delete from uniq_hits where sourceid in (select sourceid from uniq_hits
where length(sourceid::text) > 5);

or even:  delete from uniq_hits where length(sourceid::text) > 5;

but using length on numbers is usually the wrong way.

do this instead:  delete from uniq_hits where abs(sourceid) > 2^32-1;
 
Which will hit all the ones that can't be converted.
 
You may want to do a select first to see what you're deleting.
 
> I haven't had much luck with the length or char_length functions on
> postgres.

The length functions only work with strings. using them on numbers is
usually the wrong thing as there is not a 1 to 1 mapping between
strings an numbers. 

Strings of length only 3 can be out of range for integer (eg: '9e9'),
(but numerics never look like that, larger floats can though)

care to guess the result of this query?

 select '9000'::float, length('9000'::float::text);

-- 
⚂⚃ 100% natural


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