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