Thread: composite type and assignment in plpgsql
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
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