composite type and assignment in plpgsql

Ivan Sergio Borgonovo wrote:

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( ) ) );

- 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.

return thisSession;
end;
' language plpgsql;

thx

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

And then you can get the results:
select * from getsessionid(1);
imperial=# select * from getsessionid(1);
ty_found | ty_session
----------+----------------------------------
t | cf76cca2b562a0ead48d3eb3810f51cc
(1 row)

hth

Ron

Ron St-Pierre wrote:

Ivan Sergio Borgonovo wrote:

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( ) ) );

- 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.

return thisSession;
end;
' language plpgsql;

thx

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

And then you can get the results:
select * from getsessionid(1);
imperial=# select * from getsessionid(1);
ty_found | ty_session
----------+----------------------------------
t | cf76cca2b562a0ead48d3eb3810f51cc
(1 row)

hth

Ron

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

On Tue, 27 Apr 2004 10:12:13 -0700
Ron St-Pierre <rstpierre@syscor.com> wrote:

Ivan Sergio Borgonovo wrote:

--HERE!!!
thisSession := ( ''t'', md5( now( ) || rand( ) ) );

- md5 takes TEXT as an argument, not a numeric type

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

test1=# select md5( now( ) || random( ) );
md5
----------------------------------
154e804967451148bba5f28e044be828
(1 row)

and

test1=# select md5( random( ) );
md5
----------------------------------
31313f537b69d5ffe61be024a40b807e
(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

Ivan Sergio Borgonovo wrote:

On Tue, 27 Apr 2004 10:12:13 -0700

thisSession := ( ''t'', md5( now( ) || rand( ) ) );

- md5 takes TEXT as an argument, not a numeric type

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

test1=# select md5( now( ) || random( ) );
md5
----------------------------------
154e804967451148bba5f28e044be828
(1 row)

and

test1=# select md5( random( ) );
md5
----------------------------------
31313f537b69d5ffe61be024a40b807e
(1 row)

and they worked.

Yeah, they worked for me too. I was just looking at the docs and saw the
TEXT argument.........

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?

I don't know.....

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?

I'm not sure I understand what you're asking here. CURRENT_TIMESTAMP 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