Thread: PHP array to PlPgSQL arrat. How to?
Hi, I need to transform an PHP array to an PlPgSQL array. The PHP array is like this: [quote] $arr = array( 0 => array( "base64" => "ddfffffff", "image_type" => "jpg", "width" => "343", "height" => "515", "html_width_height" => 'width="343" height="515"', "mime" => "image/jpeg" ), 1 => array( "base64" => "ddfffffffddddd", "image_type" => "jpg", "width" => "343", "height" => "515", "html_width_height" => 'width="343" height="515"', "mime" => "image/jpeg" ) ); [/quote] How can I pass this kinf of PHP array to PlPgSQL? Give me a clue. Best Regards,
Hello there isn't a simple way :( the most simply way is using string_to_array function SELECT func(string_to_array('1,2,3,4,5',',')); Regards Pavel Stehule 2011/3/5 Andre Lopes <lopes80andre@gmail.com>: > Hi, > > I need to transform an PHP array to an PlPgSQL array. The PHP array is > like this: > > [quote] > $arr = array( > 0 => array( > "base64" => "ddfffffff", > "image_type" => "jpg", > "width" => "343", > "height" => "515", > "html_width_height" => 'width="343" height="515"', > "mime" => "image/jpeg" > ), > 1 => array( > "base64" => "ddfffffffddddd", > "image_type" => "jpg", > "width" => "343", > "height" => "515", > "html_width_height" => 'width="343" height="515"', > "mime" => "image/jpeg" > ) > ); > [/quote] > > How can I pass this kinf of PHP array to PlPgSQL? > > Give me a clue. > > Best Regards, > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hi Pavel, Thanks for the reply. In PlpgSQL there is possible to define arrays with "Key => Value, Key => Value"? Best Regards, On Sat, Mar 5, 2011 at 7:28 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > there isn't a simple way :( > > the most simply way is using string_to_array function > > SELECT func(string_to_array('1,2,3,4,5',',')); > > Regards > > Pavel Stehule > > > 2011/3/5 Andre Lopes <lopes80andre@gmail.com>: >> Hi, >> >> I need to transform an PHP array to an PlPgSQL array. The PHP array is >> like this: >> >> [quote] >> $arr = array( >> 0 => array( >> "base64" => "ddfffffff", >> "image_type" => "jpg", >> "width" => "343", >> "height" => "515", >> "html_width_height" => 'width="343" height="515"', >> "mime" => "image/jpeg" >> ), >> 1 => array( >> "base64" => "ddfffffffddddd", >> "image_type" => "jpg", >> "width" => "343", >> "height" => "515", >> "html_width_height" => 'width="343" height="515"', >> "mime" => "image/jpeg" >> ) >> ); >> [/quote] >> >> How can I pass this kinf of PHP array to PlPgSQL? >> >> Give me a clue. >> >> Best Regards, >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >
2011/3/5 Andre Lopes <lopes80andre@gmail.com>: > Hi Pavel, > > Thanks for the reply. > > In PlpgSQL there is possible to define arrays with "Key => Value, Key => Value"? > No, no directly there are no hash array there is a workaround a hstore module http://www.postgresql.org/docs/9.0/static/hstore.html attention - it doesn't allow a nested values Regards Pavel Stehule for more complex values is other was - using a temp tables - you can fill a temp table and in next step a plpgsql code use this temp table. But it should have a performance impacts. > Best Regards, > > > On Sat, Mar 5, 2011 at 7:28 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> Hello >> >> there isn't a simple way :( >> >> the most simply way is using string_to_array function >> >> SELECT func(string_to_array('1,2,3,4,5',',')); >> >> Regards >> >> Pavel Stehule >> >> >> 2011/3/5 Andre Lopes <lopes80andre@gmail.com>: >>> Hi, >>> >>> I need to transform an PHP array to an PlPgSQL array. The PHP array is >>> like this: >>> >>> [quote] >>> $arr = array( >>> 0 => array( >>> "base64" => "ddfffffff", >>> "image_type" => "jpg", >>> "width" => "343", >>> "height" => "515", >>> "html_width_height" => 'width="343" height="515"', >>> "mime" => "image/jpeg" >>> ), >>> 1 => array( >>> "base64" => "ddfffffffddddd", >>> "image_type" => "jpg", >>> "width" => "343", >>> "height" => "515", >>> "html_width_height" => 'width="343" height="515"', >>> "mime" => "image/jpeg" >>> ) >>> ); >>> [/quote] >>> >>> How can I pass this kinf of PHP array to PlPgSQL? >>> >>> Give me a clue. >>> >>> Best Regards, >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >
2011/3/5 Pavel Stehule <pavel.stehule@gmail.com>
2011/3/5 Andre Lopes <lopes80andre@gmail.com>:> Hi Pavel,No, no directly
>
> Thanks for the reply.
>
> In PlpgSQL there is possible to define arrays with "Key => Value, Key => Value"?
>
there are no hash array
there is a workaround a hstore module
Why hstore is a workaround ? hstore is a proven and
mature module.
And since 9.0 is has a lot of improvements and
make the life easy in some cases today and here.
mature module.
And since 9.0 is has a lot of improvements and
make the life easy in some cases today and here.
http://www.postgresql.org/docs/9.0/static/hstore.html
attention - it doesn't allow a nested values
Regards
Pavel Stehule
for more complex values is other was - using a temp tables - you can
fill a temp table and in next step a plpgsql code use this temp table.
But it should have a performance impacts.
> Best Regards,
>
>
> On Sat, Mar 5, 2011 at 7:28 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> Hello
>>
>> there isn't a simple way :(
>>
>> the most simply way is using string_to_array function
>>
>> SELECT func(string_to_array('1,2,3,4,5',','));
>>
>> Regards
>>
>> Pavel Stehule
>>
>>
>> 2011/3/5 Andre Lopes <lopes80andre@gmail.com>:
>>> Hi,
>>>
>>> I need to transform an PHP array to an PlPgSQL array. The PHP array is
>>> like this:
>>>
>>> [quote]
>>> $arr = array(
>>> 0 => array(
>>> "base64" => "ddfffffff",
>>> "image_type" => "jpg",
>>> "width" => "343",
>>> "height" => "515",
>>> "html_width_height" => 'width="343" height="515"',
>>> "mime" => "image/jpeg"
>>> ),
>>> 1 => array(
>>> "base64" => "ddfffffffddddd",
>>> "image_type" => "jpg",
>>> "width" => "343",
>>> "height" => "515",
>>> "html_width_height" => 'width="343" height="515"',
>>> "mime" => "image/jpeg"
>>> )
>>> );
>>> [/quote]
>>>
>>> How can I pass this kinf of PHP array to PlPgSQL?
>>>
>>> Give me a clue.
>>>
>>> Best Regards,
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
Thanks again, I saw this tutorial on how to get Key => Value, http://justatheory.com/computers/databases/postgresql/key-value-pairs.html It is not possible to use hstore to me. Best Regards, On Sat, Mar 5, 2011 at 7:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2011/3/5 Andre Lopes <lopes80andre@gmail.com>: >> Hi Pavel, >> >> Thanks for the reply. >> >> In PlpgSQL there is possible to define arrays with "Key => Value, Key => Value"? >> > > No, no directly > > there are no hash array > > there is a workaround a hstore module > > http://www.postgresql.org/docs/9.0/static/hstore.html > > attention - it doesn't allow a nested values > > Regards > > Pavel Stehule > > for more complex values is other was - using a temp tables - you can > fill a temp table and in next step a plpgsql code use this temp table. > But it should have a performance impacts. > > > >> Best Regards, >> >> >> On Sat, Mar 5, 2011 at 7:28 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>> Hello >>> >>> there isn't a simple way :( >>> >>> the most simply way is using string_to_array function >>> >>> SELECT func(string_to_array('1,2,3,4,5',',')); >>> >>> Regards >>> >>> Pavel Stehule >>> >>> >>> 2011/3/5 Andre Lopes <lopes80andre@gmail.com>: >>>> Hi, >>>> >>>> I need to transform an PHP array to an PlPgSQL array. The PHP array is >>>> like this: >>>> >>>> [quote] >>>> $arr = array( >>>> 0 => array( >>>> "base64" => "ddfffffff", >>>> "image_type" => "jpg", >>>> "width" => "343", >>>> "height" => "515", >>>> "html_width_height" => 'width="343" height="515"', >>>> "mime" => "image/jpeg" >>>> ), >>>> 1 => array( >>>> "base64" => "ddfffffffddddd", >>>> "image_type" => "jpg", >>>> "width" => "343", >>>> "height" => "515", >>>> "html_width_height" => 'width="343" height="515"', >>>> "mime" => "image/jpeg" >>>> ) >>>> ); >>>> [/quote] >>>> >>>> How can I pass this kinf of PHP array to PlPgSQL? >>>> >>>> Give me a clue. >>>> >>>> Best Regards, >>>> >>>> -- >>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-general >>>> >>> >> >
2011/3/5 Dmitriy Igrishin <dmitigr@gmail.com>: > > > 2011/3/5 Pavel Stehule <pavel.stehule@gmail.com> >> >> 2011/3/5 Andre Lopes <lopes80andre@gmail.com>: >> > Hi Pavel, >> > >> > Thanks for the reply. >> > >> > In PlpgSQL there is possible to define arrays with "Key => Value, Key => >> > Value"? >> > >> >> No, no directly >> >> there are no hash array >> >> there is a workaround a hstore module > > Why hstore is a workaround ? hstore is a proven and > mature module. > And since 9.0 is has a lot of improvements and > make the life easy in some cases today and here. It doesn't allow a nested hstore values. Regards Pavel >> >> http://www.postgresql.org/docs/9.0/static/hstore.html >> >> attention - it doesn't allow a nested values >> >> Regards >> >> Pavel Stehule >> >> for more complex values is other was - using a temp tables - you can >> fill a temp table and in next step a plpgsql code use this temp table. >> But it should have a performance impacts. >> >> >> >> > Best Regards, >> > >> > >> > On Sat, Mar 5, 2011 at 7:28 PM, Pavel Stehule <pavel.stehule@gmail.com> >> > wrote: >> >> Hello >> >> >> >> there isn't a simple way :( >> >> >> >> the most simply way is using string_to_array function >> >> >> >> SELECT func(string_to_array('1,2,3,4,5',',')); >> >> >> >> Regards >> >> >> >> Pavel Stehule >> >> >> >> >> >> 2011/3/5 Andre Lopes <lopes80andre@gmail.com>: >> >>> Hi, >> >>> >> >>> I need to transform an PHP array to an PlPgSQL array. The PHP array is >> >>> like this: >> >>> >> >>> [quote] >> >>> $arr = array( >> >>> 0 => array( >> >>> "base64" => >> >>> "ddfffffff", >> >>> "image_type" => >> >>> "jpg", >> >>> "width" => >> >>> "343", >> >>> "height" => >> >>> "515", >> >>> >> >>> "html_width_height" => 'width="343" height="515"', >> >>> "mime" => >> >>> "image/jpeg" >> >>> ), >> >>> 1 => array( >> >>> "base64" => >> >>> "ddfffffffddddd", >> >>> "image_type" => >> >>> "jpg", >> >>> "width" => >> >>> "343", >> >>> "height" => >> >>> "515", >> >>> >> >>> "html_width_height" => 'width="343" height="515"', >> >>> "mime" => >> >>> "image/jpeg" >> >>> ) >> >>> ); >> >>> [/quote] >> >>> >> >>> How can I pass this kinf of PHP array to PlPgSQL? >> >>> >> >>> Give me a clue. >> >>> >> >>> Best Regards, >> >>> >> >>> -- >> >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> >>> To make changes to your subscription: >> >>> http://www.postgresql.org/mailpref/pgsql-general >> >>> >> >> >> > >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > > -- > // Dmitriy. > > >
2011/3/5 Pavel Stehule <pavel.stehule@gmail.com>
2011/3/5 Dmitriy Igrishin <dmitigr@gmail.com>:>It doesn't allow a nested hstore values.
>
> 2011/3/5 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> 2011/3/5 Andre Lopes <lopes80andre@gmail.com>:
>> > Hi Pavel,
>> >
>> > Thanks for the reply.
>> >
>> > In PlpgSQL there is possible to define arrays with "Key => Value, Key =>
>> > Value"?
>> >
>>
>> No, no directly
>>
>> there are no hash array
>>
>> there is a workaround a hstore module
>
> Why hstore is a workaround ? hstore is a proven and
> mature module.
> And since 9.0 is has a lot of improvements and
> make the life easy in some cases today and here.
How so ?
dmitigr=> select ('id=>1, dat=>"id=>100"'::hstore -> 'dat')::hstore->'id' id;
id
-----
100
(1 row)
dmitigr=> select ('id=>1, dat=>"id=>100"'::hstore -> 'dat')::hstore->'id' id;
id
-----
100
(1 row)
Regards
Pavel
>>
>> http://www.postgresql.org/docs/9.0/static/hstore.html
>>
>> attention - it doesn't allow a nested values
>>
>> Regards
>>
>> Pavel Stehule
>>
>> for more complex values is other was - using a temp tables - you can
>> fill a temp table and in next step a plpgsql code use this temp table.
>> But it should have a performance impacts.
>>
>>
>>
>> > Best Regards,
>> >
>> >
>> > On Sat, Mar 5, 2011 at 7:28 PM, Pavel Stehule <pavel.stehule@gmail.com>
>> > wrote:
>> >> Hello
>> >>
>> >> there isn't a simple way :(
>> >>
>> >> the most simply way is using string_to_array function
>> >>
>> >> SELECT func(string_to_array('1,2,3,4,5',','));
>> >>
>> >> Regards
>> >>
>> >> Pavel Stehule
>> >>
>> >>
>> >> 2011/3/5 Andre Lopes <lopes80andre@gmail.com>:
>> >>> Hi,
>> >>>
>> >>> I need to transform an PHP array to an PlPgSQL array. The PHP array is
>> >>> like this:
>> >>>
>> >>> [quote]
>> >>> $arr = array(
>> >>> 0 => array(
>> >>> "base64" =>
>> >>> "ddfffffff",
>> >>> "image_type" =>
>> >>> "jpg",
>> >>> "width" =>
>> >>> "343",
>> >>> "height" =>
>> >>> "515",
>> >>>
>> >>> "html_width_height" => 'width="343" height="515"',
>> >>> "mime" =>
>> >>> "image/jpeg"
>> >>> ),
>> >>> 1 => array(
>> >>> "base64" =>
>> >>> "ddfffffffddddd",
>> >>> "image_type" =>
>> >>> "jpg",
>> >>> "width" =>
>> >>> "343",
>> >>> "height" =>
>> >>> "515",
>> >>>
>> >>> "html_width_height" => 'width="343" height="515"',
>> >>> "mime" =>
>> >>> "image/jpeg"
>> >>> )
>> >>> );
>> >>> [/quote]
>> >>>
>> >>> How can I pass this kinf of PHP array to PlPgSQL?
>> >>>
>> >>> Give me a clue.
>> >>>
>> >>> Best Regards,
>> >>>
>> >>> --
>> >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> >>> To make changes to your subscription:
>> >>> http://www.postgresql.org/mailpref/pgsql-general
>> >>>
>> >>
>> >
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> // Dmitriy.
>
>
>
--
// Dmitriy.
2011/3/5 Dmitriy Igrishin <dmitigr@gmail.com>: > > > 2011/3/5 Pavel Stehule <pavel.stehule@gmail.com> >> >> 2011/3/5 Dmitriy Igrishin <dmitigr@gmail.com>: >> > >> > >> > 2011/3/5 Pavel Stehule <pavel.stehule@gmail.com> >> >> >> >> 2011/3/5 Andre Lopes <lopes80andre@gmail.com>: >> >> > Hi Pavel, >> >> > >> >> > Thanks for the reply. >> >> > >> >> > In PlpgSQL there is possible to define arrays with "Key => Value, Key >> >> > => >> >> > Value"? >> >> > >> >> >> >> No, no directly >> >> >> >> there are no hash array >> >> >> >> there is a workaround a hstore module >> > >> > Why hstore is a workaround ? hstore is a proven and >> > mature module. >> > And since 9.0 is has a lot of improvements and >> > make the life easy in some cases today and here. >> >> It doesn't allow a nested hstore values. > > How so ? > > dmitigr=> select ('id=>1, dat=>"id=>100"'::hstore -> 'dat')::hstore->'id' > id; > id > ----- > 100 > (1 row) you cannot do some like 'a => { aa=>1, ba=>2}, c=>10, d => { a => 11, b => 12}'::hstore your solution is trick, but it doesn't help too much with deserialisation of PHP serialised structured value. I have nothing against hstore - it's best for substitution of EAV and good for almost all database use cases. But it hasn't a functionality of hash tables from Perl or PHP. Regards Pavel > >> >> Regards >> >> Pavel >> >> >> >> >> >> http://www.postgresql.org/docs/9.0/static/hstore.html >> >> >> >> attention - it doesn't allow a nested values >> >> >> >> Regards >> >> >> >> Pavel Stehule >> >> >> >> for more complex values is other was - using a temp tables - you can >> >> fill a temp table and in next step a plpgsql code use this temp table. >> >> But it should have a performance impacts. >> >> >> >> >> >> >> >> > Best Regards, >> >> > >> >> > >> >> > On Sat, Mar 5, 2011 at 7:28 PM, Pavel Stehule >> >> > <pavel.stehule@gmail.com> >> >> > wrote: >> >> >> Hello >> >> >> >> >> >> there isn't a simple way :( >> >> >> >> >> >> the most simply way is using string_to_array function >> >> >> >> >> >> SELECT func(string_to_array('1,2,3,4,5',',')); >> >> >> >> >> >> Regards >> >> >> >> >> >> Pavel Stehule >> >> >> >> >> >> >> >> >> 2011/3/5 Andre Lopes <lopes80andre@gmail.com>: >> >> >>> Hi, >> >> >>> >> >> >>> I need to transform an PHP array to an PlPgSQL array. The PHP array >> >> >>> is >> >> >>> like this: >> >> >>> >> >> >>> [quote] >> >> >>> $arr = array( >> >> >>> 0 => array( >> >> >>> "base64" => >> >> >>> "ddfffffff", >> >> >>> "image_type" >> >> >>> => >> >> >>> "jpg", >> >> >>> "width" => >> >> >>> "343", >> >> >>> "height" => >> >> >>> "515", >> >> >>> >> >> >>> "html_width_height" => 'width="343" height="515"', >> >> >>> "mime" => >> >> >>> "image/jpeg" >> >> >>> ), >> >> >>> 1 => array( >> >> >>> "base64" => >> >> >>> "ddfffffffddddd", >> >> >>> "image_type" >> >> >>> => >> >> >>> "jpg", >> >> >>> "width" => >> >> >>> "343", >> >> >>> "height" => >> >> >>> "515", >> >> >>> >> >> >>> "html_width_height" => 'width="343" height="515"', >> >> >>> "mime" => >> >> >>> "image/jpeg" >> >> >>> ) >> >> >>> ); >> >> >>> [/quote] >> >> >>> >> >> >>> How can I pass this kinf of PHP array to PlPgSQL? >> >> >>> >> >> >>> Give me a clue. >> >> >>> >> >> >>> Best Regards, >> >> >>> >> >> >>> -- >> >> >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> >> >>> To make changes to your subscription: >> >> >>> http://www.postgresql.org/mailpref/pgsql-general >> >> >>> >> >> >> >> >> > >> >> >> >> -- >> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-general >> > >> > >> > >> > -- >> > // Dmitriy. >> > >> > >> > > > > > -- > // Dmitriy. > > >
On 03/05/11 11:22 AM, Andre Lopes wrote: > Hi, > > I need to transform an PHP array to an PlPgSQL array. The PHP array is > like this: in a relational database, it would be far better to store that sort of thing as a table, so you can use relational operations on it. your example structure would fit nicely into a table like... CREATE TABLE images ( id integer primary key, base64 text, image_type text, width integer, height integer, mime text ); and use a view to construct the html_width_height value as it contains redundant data CREATE VIEW images_html (id, html_width_height) as select id, 'width="'||cast(width as text)||'" height="'||cast(height as text)||'"' from images; btw, if that base64 field is in fact the binary image, I would instead change that to `image bytea` and store the image in binary.
Hi, Just another question about this subject. It is possible to compare if these 3 PlpgSQL arrays have the same number of elements? How to get the number of elements of an PLpgSQL array? NOTICE: file types: {image,image,image,image,image} NOTICE: file details: {type,width,height,html_width_height,mime} NOTICE: values: {jpg,343,515,"width=\"343\" height=\"515\"",image/jpeg} Best Regards, On Sat, Mar 5, 2011 at 10:44 PM, John R Pierce <pierce@hogranch.com> wrote: > On 03/05/11 11:22 AM, Andre Lopes wrote: >> >> Hi, >> >> I need to transform an PHP array to an PlPgSQL array. The PHP array is >> like this: > > in a relational database, it would be far better to store that sort of thing > as a table, so you can use relational operations on it. > > your example structure would fit nicely into a table like... > > CREATE TABLE images ( > id integer primary key, > base64 text, > image_type text, > width integer, > height integer, > mime text ); > > and use a view to construct the html_width_height value as it contains > redundant data > > CREATE VIEW images_html (id, html_width_height) > as select id, 'width="'||cast(width as text)||'" height="'||cast(height > as text)||'"' from images; > > > > > btw, if that base64 field is in fact the binary image, I would instead > change that to `image bytea` and store the image in binary. > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hello look on array_lower and array_upper functions http://www.postgresql.org/docs/8.2/static/functions-array.html for one dimensional arrays - select array_upper(var,1) - array_lower(var,1) Regards Pavel Stehule 2011/3/6 Andre Lopes <lopes80andre@gmail.com>: > Hi, > > Just another question about this subject. > > It is possible to compare if these 3 PlpgSQL arrays have the same > number of elements? How to get the number of elements of an PLpgSQL > array? > > NOTICE: file types: {image,image,image,image,image} > NOTICE: file details: {type,width,height,html_width_height,mime} > NOTICE: values: {jpg,343,515,"width=\"343\" height=\"515\"",image/jpeg} > > Best Regards, > > > > > > > On Sat, Mar 5, 2011 at 10:44 PM, John R Pierce <pierce@hogranch.com> wrote: >> On 03/05/11 11:22 AM, Andre Lopes wrote: >>> >>> Hi, >>> >>> I need to transform an PHP array to an PlPgSQL array. The PHP array is >>> like this: >> >> in a relational database, it would be far better to store that sort of thing >> as a table, so you can use relational operations on it. >> >> your example structure would fit nicely into a table like... >> >> CREATE TABLE images ( >> id integer primary key, >> base64 text, >> image_type text, >> width integer, >> height integer, >> mime text ); >> >> and use a view to construct the html_width_height value as it contains >> redundant data >> >> CREATE VIEW images_html (id, html_width_height) >> as select id, 'width="'||cast(width as text)||'" height="'||cast(height >> as text)||'"' from images; >> >> >> >> >> btw, if that base64 field is in fact the binary image, I would instead >> change that to `image bytea` and store the image in binary. >> >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hi Pavel, Thanks for the reply. It was exactly that that I'm looking for. Best Regards, On Sun, Mar 6, 2011 at 3:05 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > look on array_lower and array_upper functions > > http://www.postgresql.org/docs/8.2/static/functions-array.html > > for one dimensional arrays - select array_upper(var,1) - array_lower(var,1) > > Regards > > Pavel Stehule > > 2011/3/6 Andre Lopes <lopes80andre@gmail.com>: >> Hi, >> >> Just another question about this subject. >> >> It is possible to compare if these 3 PlpgSQL arrays have the same >> number of elements? How to get the number of elements of an PLpgSQL >> array? >> >> NOTICE: file types: {image,image,image,image,image} >> NOTICE: file details: {type,width,height,html_width_height,mime} >> NOTICE: values: {jpg,343,515,"width=\"343\" height=\"515\"",image/jpeg} >> >> Best Regards, >> >> >> >> >> >> >> On Sat, Mar 5, 2011 at 10:44 PM, John R Pierce <pierce@hogranch.com> wrote: >>> On 03/05/11 11:22 AM, Andre Lopes wrote: >>>> >>>> Hi, >>>> >>>> I need to transform an PHP array to an PlPgSQL array. The PHP array is >>>> like this: >>> >>> in a relational database, it would be far better to store that sort of thing >>> as a table, so you can use relational operations on it. >>> >>> your example structure would fit nicely into a table like... >>> >>> CREATE TABLE images ( >>> id integer primary key, >>> base64 text, >>> image_type text, >>> width integer, >>> height integer, >>> mime text ); >>> >>> and use a view to construct the html_width_height value as it contains >>> redundant data >>> >>> CREATE VIEW images_html (id, html_width_height) >>> as select id, 'width="'||cast(width as text)||'" height="'||cast(height >>> as text)||'"' from images; >>> >>> >>> >>> >>> btw, if that base64 field is in fact the binary image, I would instead >>> change that to `image bytea` and store the image in binary. >>> >>> >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >