composite type and assignment in plpgsql

[Ivan Sergio Borgonovo]

what's wrong with this?

create type tSession
as ( ty_found boolean, ty_Session char(32) );

create or replace function GetSessionID( integer )
returns tSession as '
declare
thisSession tSession;
begin
--HERE!!!
thisSession := ( ''t'', md5( now( ) || rand( ) ) );
return thisSession;
end;
' language plpgsql;


thx


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Ron St-Pierre]

Re: composite type and assignment in plpgsql

Ivan Sergio Borgonovo wrote:
[color=blue]
>what's wrong with this?
>
>create type tSession
> as ( ty_found boolean, ty_Session char(32) );
>
>create or replace function GetSessionID( integer )
> returns tSession as '
>declare
> thisSession tSession;
>begin
> --HERE!!!
> thisSession := ( ''t'', md5( now( ) || rand( ) ) );
>[/color]
- md5 takes TEXT as an argument, not a numeric type
- assign each variable of type tSession to its corresponding value:
thisSession.ty_ found := ''t'';
thisSession.ty_ session := md5(CAST((now( )) AS TEXT));
I haven't looked up the rand() function, but you can see from this how
you would cast it and now() to text.
[color=blue]
> return thisSession;
>end;
>' language plpgsql;
>
>
>thx
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>[/color]
And then you can get the results:
select * from getsessionid(1) ;
imperial=# select * from getsessionid(1) ;
ty_found | ty_session
----------+----------------------------------
t | cf76cca2b562a0e ad48d3eb3810f51 cc
(1 row)


hth

Ron



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

[Ron St-Pierre]

Re: composite type and assignment in plpgsql

Ivan Sergio Borgonovo wrote:
[color=blue]
>what's wrong with this?
>
>create type tSession
> as ( ty_found boolean, ty_Session char(32) );
>
>create or replace function GetSessionID( integer )
> returns tSession as '
>declare
> thisSession tSession;
>begin
> --HERE!!!
> thisSession := ( ''t'', md5( now( ) || rand( ) ) );
>[/color]
- md5 takes TEXT as an argument, not a numeric type
- assign each variable of type tSession to its corresponding value:
thisSession.ty_ found := ''t'';
thisSession.ty_ session := md5(CAST((now( )) AS TEXT));
I haven't looked up the rand() function, but you can see from this how
you would cast it and now() to text.
[color=blue]
> return thisSession;
>end;
>' language plpgsql;
>
>
>thx
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>[/color]
And then you can get the results:
select * from getsessionid(1) ;
imperial=# select * from getsessionid(1) ;
ty_found | ty_session
----------+----------------------------------
t | cf76cca2b562a0e ad48d3eb3810f51 cc
(1 row)


hth

Ron



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

[Ron St-Pierre]

Re: composite type and assignment in plpgsql

Ron St-Pierre wrote:
[color=blue]
> Ivan Sergio Borgonovo wrote:
>[color=green]
>> what's wrong with this?
>>
>> create type tSession
>> as ( ty_found boolean, ty_Session char(32) );
>>
>> create or replace function GetSessionID( integer )
>> returns tSession as '
>> declare
>> thisSession tSession;
>> begin
>> --HERE!!!
>> thisSession := ( ''t'', md5( now( ) || rand( ) ) );
>>[/color]
> - md5 takes TEXT as an argument, not a numeric type
> - assign each variable of type tSession to its corresponding value:
> thisSession.ty_ found := ''t'';
> thisSession.ty_ session := md5(CAST((now( )) AS TEXT));
> I haven't looked up the rand() function, but you can see from this how
> you would cast it and now() to text.
>[color=green]
>> return thisSession;
>> end;
>> ' language plpgsql;
>>
>>
>> thx
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>>
>>
>>[/color]
> And then you can get the results:
> select * from getsessionid(1) ;
> imperial=# select * from getsessionid(1) ;
> ty_found | ty_session
> ----------+----------------------------------
> t | cf76cca2b562a0e ad48d3eb3810f51 cc
> (1 row)
>
>
> hth
>
> Ron
>
>[/color]
In the above reply, I forgot to mention that you are not using the
integer you are passing in as an argument. If you need it (rand()?)
you'll have to declare it:
myInt ALIAS FOR $1;
or use it explicitly with just the name: $1

Ron


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ron St-Pierre]

Re: composite type and assignment in plpgsql

Ron St-Pierre wrote:
[color=blue]
> Ivan Sergio Borgonovo wrote:
>[color=green]
>> what's wrong with this?
>>
>> create type tSession
>> as ( ty_found boolean, ty_Session char(32) );
>>
>> create or replace function GetSessionID( integer )
>> returns tSession as '
>> declare
>> thisSession tSession;
>> begin
>> --HERE!!!
>> thisSession := ( ''t'', md5( now( ) || rand( ) ) );
>>[/color]
> - md5 takes TEXT as an argument, not a numeric type
> - assign each variable of type tSession to its corresponding value:
> thisSession.ty_ found := ''t'';
> thisSession.ty_ session := md5(CAST((now( )) AS TEXT));
> I haven't looked up the rand() function, but you can see from this how
> you would cast it and now() to text.
>[color=green]
>> return thisSession;
>> end;
>> ' language plpgsql;
>>
>>
>> thx
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>>
>>
>>[/color]
> And then you can get the results:
> select * from getsessionid(1) ;
> imperial=# select * from getsessionid(1) ;
> ty_found | ty_session
> ----------+----------------------------------
> t | cf76cca2b562a0e ad48d3eb3810f51 cc
> (1 row)
>
>
> hth
>
> Ron
>
>[/color]
In the above reply, I forgot to mention that you are not using the
integer you are passing in as an argument. If you need it (rand()?)
you'll have to declare it:
myInt ALIAS FOR $1;
or use it explicitly with just the name: $1

Ron


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ivan Sergio Borgonovo]

Re: composite type and assignment in plpgsql

On Tue, 27 Apr 2004 10:12:13 -0700
Ron St-Pierre <rstpierre@sysc or.com> wrote:
[color=blue]
> Ivan Sergio Borgonovo wrote:[/color]
[color=blue][color=green]
> > --HERE!!!
> > thisSession := ( ''t'', md5( now( ) || rand( ) ) );
> >[/color]
> - md5 takes TEXT as an argument, not a numeric type[/color]

Since it works you surely fixed my code but this should't be an issue
since I tried

test1=# select md5( now( ) || random( ) );
md5
----------------------------------
154e80496745114 8bba5f28e044be8 28
(1 row)

and

test1=# select md5( random( ) );
md5
----------------------------------
31313f537b69d5f fe61be024a40b80 7e
(1 row)

and they worked.

and yeah I messed up remembering mySQL code and wrote rand( ) inspite
of random( )

Can't user composite type be initialized in a shortest way?
eg. ( ( ), ( ), , ( ), , , ( ), ...)
I thought they could. I saw a similar syntax somewhere in the docs. Am
I daydreaming?

One more thing about the first example presented in this page:

http://www.postgresql.org/docs/7.4/static/plpgsql-expressions.html

I just tried
create or replace function GetSessionID( integer )
returns tSession as '
declare
thisSession tSession;
begin
thisSession.ty_ Found := ''t'';
thisSession.ty_ Session := now( );
return thisSession;
end;
' language plpgsql;

and it returns execution time not "plan" time. Does "plan" time is
strictly referred to SQL statements?


thanks for your help


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Ivan Sergio Borgonovo]

Re: composite type and assignment in plpgsql

On Tue, 27 Apr 2004 10:12:13 -0700
Ron St-Pierre <rstpierre@sysc or.com> wrote:
[color=blue]
> Ivan Sergio Borgonovo wrote:[/color]
[color=blue][color=green]
> > --HERE!!!
> > thisSession := ( ''t'', md5( now( ) || rand( ) ) );
> >[/color]
> - md5 takes TEXT as an argument, not a numeric type[/color]

Since it works you surely fixed my code but this should't be an issue
since I tried

test1=# select md5( now( ) || random( ) );
md5
----------------------------------
154e80496745114 8bba5f28e044be8 28
(1 row)

and

test1=# select md5( random( ) );
md5
----------------------------------
31313f537b69d5f fe61be024a40b80 7e
(1 row)

and they worked.

and yeah I messed up remembering mySQL code and wrote rand( ) inspite
of random( )

Can't user composite type be initialized in a shortest way?
eg. ( ( ), ( ), , ( ), , , ( ), ...)
I thought they could. I saw a similar syntax somewhere in the docs. Am
I daydreaming?

One more thing about the first example presented in this page:

http://www.postgresql.org/docs/7.4/static/plpgsql-expressions.html

I just tried
create or replace function GetSessionID( integer )
returns tSession as '
declare
thisSession tSession;
begin
thisSession.ty_ Found := ''t'';
thisSession.ty_ Session := now( );
return thisSession;
end;
' language plpgsql;

and it returns execution time not "plan" time. Does "plan" time is
strictly referred to SQL statements?


thanks for your help


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Ron St-Pierre]

Re: composite type and assignment in plpgsql

Ivan Sergio Borgonovo wrote:
[color=blue]
>On Tue, 27 Apr 2004 10:12:13 -0700
>
>[color=green][color=darkred]
>>> thisSession := ( ''t'', md5( now( ) || rand( ) ) );
>>>
>>>[/color]
>>- md5 takes TEXT as an argument, not a numeric type
>>
>>[/color]
>
>Since it works you surely fixed my code but this should't be an issue
>since I tried
>
>test1=# select md5( now( ) || random( ) );
> md5
>----------------------------------
> 154e80496745114 8bba5f28e044be8 28
>(1 row)
>
>and
>
>test1=# select md5( random( ) );
> md5
>----------------------------------
> 31313f537b69d5f fe61be024a40b80 7e
>(1 row)
>
>and they worked.
>[/color]
Yeah, they worked for me too. I was just looking at the docs and saw the
TEXT argument....... ..
[color=blue]
>
>and yeah I messed up remembering mySQL code and wrote rand( ) inspite
>of random( )
>
>Can't user composite type be initialized in a shortest way?
>eg. ( ( ), ( ), , ( ), , , ( ), ...)
>I thought they could. I saw a similar syntax somewhere in the docs. Am
>I daydreaming?
>[/color]
I don't know.....
[color=blue]
>
>One more thing about the first example presented in this page:
>http://www.postgresql.org/docs/7.4/s...pressions.html
>
>I just tried
>create or replace function GetSessionID( integer )
> returns tSession as '
>declare
> thisSession tSession;
>begin
> thisSession.ty_ Found := ''t'';
> thisSession.ty_ Session := now( );
> return thisSession;
>end;
>' language plpgsql;
>
>and it returns execution time not "plan" time. Does "plan" time is
>strictly referred to SQL statements?
>
>[/color]
I'm not sure I understand what you're asking here. CURRENT_TIMESTA MP and
now() return the start time of the current transaction, would that be
the "plan" time? The timeofday() function returns the "wall clock" time
and advances during transactions. I think that this would be the
"execution" time.

Hope that helps
Ron


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Ron St-Pierre]

Re: composite type and assignment in plpgsql

Ivan Sergio Borgonovo wrote:
[color=blue]
>On Tue, 27 Apr 2004 10:12:13 -0700
>
>[color=green][color=darkred]
>>> thisSession := ( ''t'', md5( now( ) || rand( ) ) );
>>>
>>>[/color]
>>- md5 takes TEXT as an argument, not a numeric type
>>
>>[/color]
>
>Since it works you surely fixed my code but this should't be an issue
>since I tried
>
>test1=# select md5( now( ) || random( ) );
> md5
>----------------------------------
> 154e80496745114 8bba5f28e044be8 28
>(1 row)
>
>and
>
>test1=# select md5( random( ) );
> md5
>----------------------------------
> 31313f537b69d5f fe61be024a40b80 7e
>(1 row)
>
>and they worked.
>[/color]
Yeah, they worked for me too. I was just looking at the docs and saw the
TEXT argument....... ..
[color=blue]
>
>and yeah I messed up remembering mySQL code and wrote rand( ) inspite
>of random( )
>
>Can't user composite type be initialized in a shortest way?
>eg. ( ( ), ( ), , ( ), , , ( ), ...)
>I thought they could. I saw a similar syntax somewhere in the docs. Am
>I daydreaming?
>[/color]
I don't know.....
[color=blue]
>
>One more thing about the first example presented in this page:
>http://www.postgresql.org/docs/7.4/s...pressions.html
>
>I just tried
>create or replace function GetSessionID( integer )
> returns tSession as '
>declare
> thisSession tSession;
>begin
> thisSession.ty_ Found := ''t'';
> thisSession.ty_ Session := now( );
> return thisSession;
>end;
>' language plpgsql;
>
>and it returns execution time not "plan" time. Does "plan" time is
>strictly referred to SQL statements?
>
>[/color]
I'm not sure I understand what you're asking here. CURRENT_TIMESTA MP and
now() return the start time of the current transaction, would that be
the "plan" time? The timeofday() function returns the "wall clock" time
and advances during transactions. I think that this would be the
"execution" time.

Hope that helps
Ron


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings