Обсуждение: 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