[SQL]How to transform table rows into Colum?
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 -- Eric Ndengang Datenbankadministrator Affinitas GmbH | Kohlfurter Straße 41/43 | 10999 Berlin | Germany email: eric.ndengang_fo...@affinitas.de | tel: +49.(0)30. 991 949 5 0 | www.edarling.de Geschäftsführer: Lukas Brosseder, David Khalil, Kai Rieke, Christian Vollmann Eingetragen beim Amtsgericht Berlin, HRB 115958 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL]How to transform table rows into Colum?
Hey Eric, 2011/3/9 Eric Ndengang > 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 > Please, see the complete example here: http://www.postgresql.org/docs/9.0/static/tablefunc.html > Regards > > -- > Eric Ndengang > Datenbankadministrator > > Affinitas GmbH | Kohlfurter Straße 41/43 | 10999 Berlin | Germany > email: eric.ndengang_fo...@affinitas.de | tel: +49.(0)30. 991 949 5 0 | > www.edarling.de > > Geschäftsführer: Lukas Brosseder, David Khalil, Kai Rieke, Christian > Vollmann > Eingetragen beim Amtsgericht Berlin, HRB 115958 > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- // Dmitriy.
[SQL] plpgsql exception handling
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, I cut out the unimportant pieces.
CREATE OR REPLACE FUNCTION report_process(p_sql text)
RETURNS integer AS
$BODY$
DECLARE
l_state smallint;
l_message text;
BEGIN
l_state=0;
begin
execute 'create table result_'||p_id||' as '||p_sql;
exception when others then
l_state=-3;
l_message:='Error executing sql sql error code: %, sql error
message: %, sql: %',SQLSTATE,SQLERRM,l_sql;
end;
update "cache"
set c_date=now(), c_state=l_state, c_message=l_message
where c_id=p_id;
return 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
This is the error message when I call the function
select report_process('select 1count(*) from event_log_day'::text);
ERROR: syntax error at or near "("
LINE 1: ...e table result_9 as select 1count(*) from d...
^
QUERY: create table result_9 as select 1count(*) from event_log_day
CONTEXT: PL/pgSQL function "report_process" line 31 at EXECUTE statement
** Error **
ERROR: syntax error at or near "("
SQL state: 42601
Context: PL/pgSQL function "report_process" line 31 at EXECUTE statement
Any help is appreciated.
best regards,
Uwe
Re: [SQL] plpgsql exception handling
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 generating the error. Perhaps the 1 in front of count(*) is a typo?
On Wed, Mar 9, 2011 at 1:55 PM, Uwe Bartels wrote:
> 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, I cut out the unimportant pieces.
>
> CREATE OR REPLACE FUNCTION report_process(p_sql text)
> RETURNS integer AS
> $BODY$
> DECLARE
> l_state smallint;
> l_message text;
> BEGIN
>
> l_state=0;
> begin
> execute 'create table result_'||p_id||' as '||p_sql;
> exception when others then
> l_state=-3;
> l_message:='Error executing sql sql error code: %, sql error
> message: %, sql: %',SQLSTATE,SQLERRM,l_sql;
> end;
>
> update "cache"
> set c_date=now(), c_state=l_state, c_message=l_message
> where c_id=p_id;
>
> return 0;
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE SECURITY DEFINER
> COST 100;
>
>
> This is the error message when I call the function
> select report_process('select 1count(*) from event_log_day'::text);
>
>
> ERROR: syntax error at or near "("
> LINE 1: ...e table result_9 as select 1count(*) from d...
> ^
> QUERY: create table result_9 as select 1count(*) from event_log_day
> CONTEXT: PL/pgSQL function "report_process" line 31 at EXECUTE statement
>
> ** Error **
>
> ERROR: syntax error at or near "("
> SQL state: 42601
> Context: PL/pgSQL function "report_process" line 31 at EXECUTE statement
>
> Any help is appreciated.
>
> best regards,
> Uwe
>
Re: [SQL] plpgsql exception handling
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 query you are passing to the procedure is invalid > and is generating the error. Perhaps the 1 in front of count(*) is a typo? Also seem to be missing "p_id" from your execute statement: execute 'create table result_'|| p_id ||' as '||p_sql; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
